create sp to populate table from one db to another

Question

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

Answer 1

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

Answer 2

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/

Answer 3

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.

Answer 4

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