how to write T-SQL to compare and copy data?

Question

Hello everyone,

I have two SQL Server 2008 Enterprise databases (on two machines), and one of the databases is master database and another database is slave database.

I want to transfer update from a table in source database to a table in destination database (two tables are of the same schema, both of them are using a single column as unique primary key). The transfer rule is (in short, the rule is keeping the destination database the same as source database because of the update of the source database),

1. if there is a new row in source database but not in destination database, insert the row in destination database;
2. if a row not exists in source database but exists in destination database, delete the row in destination database;
3. if a row's content (i.e. columns other than primary key columns) changes in source database, update the new content into destination database.

thanks in advance, George

Answer 1

You can use SQL server project with wizard from Visual Studio (VSTS) [Check out this tutorial to learn more], in that you can select your master database, then select your other configuration in the wizard.

Finally it will fetch your data/schema from your master database then you can compare to any other sql server database with Data or Schema compare.

It will also generates the update script and can directly apply all changes to slave database.

You need visual studio team system database edition support.