Tablediff.exe (Compare Two Datasources)

Need to compare two tables and determine what is missing from table #2?  Or, maybe you’ve got two tables on two different servers, and they need to be in synch?  The Tablediff utility was introduced with SQL v2005, and it can be used to reconcile data between two tables, or even views.  Tablediff.exe can be used to report variances in data AND schema.  Per BOL, it is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.

It can be used to perform several different comparison type tasks.  You can look at the data row by row, or you can just compare the row counts.  You can perform just the schema comparison, or the data AND schema.  In my opinion, the best feature of tablediff is that you can generate the script to actually reconcile the differences between the datasources.


I’ve read that it was actually intended for replication, but it can be easily applied to any situation where you’re trying to compare data, schema, or both.  I can’t even begin to count the hours I have spent on ‘recon’ logic within the Chicago HFT (high frequency trading)  arena…  L.

Tablediff.exe can be found at \Program Files\Microsoft SQL Server\90\COM\TableDiff.exe, where SQL Server is installed.  This example will compare two tables in two different databases on two different servers, and it will generate the file that can ultimately be used to reconcile the differences:

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver sourceservername
                                                                                        -sourcedatabase sourcedatabase
                                                                                        -sourcetable sourcetable
                                                                                        -destinationserver destinationservername 
                                                                                        -destinationdatabase destinationdatabase
                                                                                        -destinationtable destinationtable
                                                                                        -et ChangeScript
                                                                                        -f C:\Temp\ChangeScript.sql

Please take a look at BOL for more information:       
http://msdn.microsoft.com/en-us/library/ms162843.aspx