I have a different source and destination database server with the same name.
On a periodic interval, we push "new" data configuration to the source database and manually update the data in the destination database.
Instead of handling it manually, we are trying to do one time "new" data distribution from source db to the listed linked server destination db's.
I presume that if we use "Linked Server"s it can help to achieve the data distribution by validating missing (or) unavailable records in the destination database servers.
Scenario : New price data distribution from source database to the client databases. We might have new price for new products (or) new price for existing products. Based on the "new" data changes or "new" data in the source db, we will have to distribute the client db.
How do I distribute data from the source database to the destination database in SQL?
Best Answer
Replication is the right solution here, despite the comment above. Simply copy the published table into a separate staging table at each subscriber. Then, on a schedule of your choosing, copy the data from the staging table to wherever it needs to go in the subscriber database.
You can use SQL Server Transactional Replication for this, or just copy the tables on a schedule with SSIS or over Linked Server connections.
If you use SSIS, your SSIS package you can use a Foreach loop to load multiple databases, or you can configure a separate SQL Agent job or job step for each target database, which would enable you to schedule them differently, and run just one. A SQL Agent job step can specify the target connection string.