SQL Server comes with many command line tools and one new tool that is available is the tablediff command. This tool help us to perform tasks:
1. Compare the two tables.
2. Tell us where any differences occur in the data between two tables.
3. Generate T-SQL commands to get the tables back in synch.
Note: SQL codes are not executed, it will just be written to a file for us to execute if we wish.
The command takes a few basic parameters to run.
Here is an example command that compares two tables Table1 and Table2 in the same database.
Lauch the command prompt and navigate to direcotry where tablediff executable file resides
I am using the Microsoft SQL Server 2012
C:\Program Files\Microsoft SQL Server\110\COM
execute the command
tablediff.exe -sourceserver MARKDEV -sourcedatabase LCMS_DEV -sourcetable employees -destinationserver MARKUAT -destinationdatabase LCMS_UAT -destinationtable employee -et Difference -f c:\employee_differences.sql
|-et||Use this argument to see the differences in a table. The "et" parameter will create a table, in our case called "Difference", so we can see the differences in a table.|
|-f||use this argument that will create a T-SQL script to synchronize the two tables.|