Share via

create sp to populate table from one db to another

Vineet S 205 Reputation points
2024-05-21T02:56:37.6+00:00

Hi,

how to create Store procedure to populate data from one database table to another database table and do we need to refresh or it will auto sync

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,954 questions
Azure Database for PostgreSQL
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,836 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,252 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. hossein jalilian 4,385 Reputation points
    2024-05-21T03:04:06.23+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    To efficiently populate data from one database table to another and manage synchronization, using ETL tools is a recommended approach.

    • SQL Server Integration Services (SSIS)
    • Azure Data Factory (ADF)
    • Azure Databricks

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

    1 person found this answer helpful.
  2. Olaf Helper 41,886 Reputation points
    2024-05-21T05:22:16.65+00:00

    You used a lot unrelated tags for your post.

    You can use cross database access, see

    https://www.mssqltips.com/sqlservertip/2549/options-for-cross-database-access-within-sql-server/

    0 comments
  3. ZoeHui-MSFT 33,946 Reputation points
    2024-05-21T07:15:05.1333333+00:00

    Hi @Vineet S,

    Use sp to do that maybe a lot hard.

    You may use SSIS to do that.

    First you may load the table data and then Synchronize Table Data Using a Merge Join in SSIS.

    Copying data from one database to another using SSIS

    Synchronize Table Data Using a Merge Join in SSIS

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments
  4. ShaktiSingh-MSFT 14,081 Reputation points Microsoft Employee
    2024-05-24T06:02:16.3066667+00:00

    Hi Vineet S,

    Welcome to Microsoft Q&A forum.

    As I understand, you want to create Data Sync between 2 Databases.

    If you are using Azure SQL Database and want to set up sync, please refer to the Set up of SQL Data Sync as given in the below mentioned documentation:

    https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?view=azuresql

    Data Sync uses a hub and spoke topology to synchronize data. You define one of the databases in the sync group as the hub database. The rest of the databases are member databases. Sync occurs only between the hub and individual members.

    • The Hub Database must be an Azure SQL Database.
    • The member databases can be either databases in Azure SQL Database or in instances of SQL Server.
    • The Sync Metadata Database contains the metadata and log for Data Sync. The Sync Metadata Database has to be an Azure SQL Database located in the same region as the Hub Database. The Sync Metadata Database is customer created and customer owned. You can only have one Sync Metadata Database per region and subscription. Sync Metadata Database can't be deleted or renamed while sync groups or sync agents exist. Microsoft recommends creating a new, empty database for use as the Sync Metadata Database. Data Sync creates tables in this database and runs a frequent workload.

    Data Sync is useful in cases where data needs to be kept updated across several databases in Azure SQL Database or SQL Server. Here are the main use cases for Data Sync:

    • Hybrid Data Synchronization: With Data Sync, you can keep data synchronized between your databases in SQL Server and Azure SQL Database to enable hybrid applications. This capability may appeal to customers who are considering moving to the cloud and would like to put some of their application in Azure.
    • Distributed Applications: In many cases, it's beneficial to separate different workloads across different databases. For example, if you have a large production database, but you also need to run a reporting or analytics workload on this data, it's helpful to have a second database for this extra workload. This approach minimizes the performance impact on your production workload. You can use Data Sync to keep these two databases synchronized.
    • Globally Distributed Applications: Many businesses span several regions and even several countries/regions. To minimize network latency, it's best to have your data in a region close to you. With Data Sync, you can easily keep databases in regions around the world synchronized.

    Check limitations here:

    https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql#general-limitations

    Let us know if this fulfils your requirement.

    Thanks

    0 comments