Microsoft ships a TableDiff utility along with SQL Server. This utility compares the contents of two tables, and outputs the results of the comparison in different ways depending on settings past by the client. Tablediff can be used as part of your replication strategy confirming success or failure.
The tablediff utility does have some restrictions. It only works with SQL Server tables. It doesn’t like SQL Variant data type, and it has limited implicit conversion if the data types of the two tables being compared are different.
That being said, the tool looks especially useful. It can do a schema only comparison, row count comparison, or even a row by row comparison of each column. The results may be output into a file, or a table in the destination database. It also has the ability to generate a change script to synchronized the contents of the destination table. It doesn’t seem like there is much more to do with this kind of requirement.
You can take a detailed look at the tablediff utility to find our more about how it works and what it does. Details from Microsoft are found at http://technet.microsoft.com/en-us/library/ms162843(v=sql.100).aspx.
Have you used tablediff? Is it integrated into your replication processes? Share your experience here online, or drop an email to btaylor@sswug.org.
Cheers,
Ben