An application developer asked me if there was an option in SQL Server to compare the contents/data of two tables without having to create a custom application. There are various third party tools that can achieve this target however SQL Server comes with an out-of-the-box solution.
The SQL Server Replication Diff Tool (or tablediff Utility) is intended to be used in a replicated environment however it can be invoked from the command line to compare the contents of two tables and it will also generate a script to bring the destination in sync with the source.
The executable can be found in the “<INSTALL_DIR>\90\COM” folder for SQL Server 2005 or the “<INSTALL_DIR>\100\COM” folder in the case of SQL Server 2008. Executing tablediff/? in a command prompt will display the information shown below:
Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft Corporation User-specified agent parameter values: /? Replication Diff Tool Command Line Options usage: tablediff -- Source Options -- -sourceserver Source Host -sourcedatabase Source Database -sourceschema Source Schema Name -sourcetable Source Table or View -sourceuser Source Login -sourcepassword Source Password -sourcelocked Lock the source table/view durring tablediff -- Destination Options -- -destinationserver Destination Host -destinationdatabase Destination Database -destinationschema Destination Schema Name -destinationtable Destination Table or View -destinationuser Destination Login -destinationpassword Destination Password -destinationlocked Lock the destination table/view durring tablediff -- Misc Options -- -t Timeout -c Column Level Diff -f Generate Fix SQL (You may also specify a file name and path) -q Quick Row Count -et Specify a table to put the errors into -dt Drop the error table if it exists -o Output file -b Number of bytes to read for blob data types -strict Strict compare of source and destination schema -rc Number of retries -ri Retry interval