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