SQL Data Tools - Compare Data

Compare Data between two tables

SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.

                                  Figure: SQL Server Data Tools can be handier than any of these tools

Visual Studio SQL Data Tools

Tool -> SQL  Server -> New Data Compare

Then we have to select source and destination. Then check the options you want to compare

Then select the objects you want to compare (table or view)
After comparison, you can output something like this - 

• Different Records - Records that exist in both source and destination. These will be updated in destination
• Only In Source - Data in Source DB, NOT in Destination, these data need to be added
• Only in Destination - Data in Destination DB, NOT in source, these data need to be deleted
• Identical Records - Data Identical in both source and destination.

Click on generate scripts, you will get a script for this data migration :D 

You can run it in Visual Studio Or SSMS

Run this script on destination to make it the same as the source

This script performs its actions in the following order:

• Disable foreign-key constraints.
•  Perform DELETE commands. 
• Perform UPDATE commands.
• Perform INSERT commands.
• Re-enable foreign-key constraints.

Please back up your target database before running this script. Because you know, murphy's law states that, "If anything can go wrong, it will"