Using the tablediff command line utility in SQL Server
The tablediff command line utitlity provides us with the ability to compare the content of two tables in a database. It not only tells us which records / columns are different but can also generate a SQL script to update the second table to make it the same as the first table.
The tablediff utility is new to SQL Server 2005 and was orginally designed to help verify / troubleshoot SQL Server replication routines. It can however be used in any scenario where the data in two tables should be the same or very similar.
The syntax for tablediff looks quite daunting so we will first create an example from scratch and then at the end of the article give you the full syntax so you can explore further.
tablediff example from scratch
First lets create a basic table and add some dummy data to it:
CREATE TABLE Customers ( CustomerID int IDENTITY(1,1) NOT NULL, Name varchar(50), Telephone varchar(30) )
Add some records to the table:
Make a copy of this table and give it the name of CustomersBackup using the SELECT INTO statement:
Now change some data in the CustomersBackup table:
Now we have two tables with the same structure that each hold 3 records. 2 of the records in CustomersBackup have had the data in them changed. Now lets use tablediff to:
- Compare the two tables
- Tell us where any differences occur in the data
- Generate the SQL to bring the data in the two tables the same - note SQL code is not executed, it will just be written for us to execute if we wish.
First in the command prompt navigate to the directory where the tablediff executable file resides. You may need to change the drive letter below::
Now execute the following command (note that the following command should all be on one line of the command prompt, it is split onto different lines below to help readability).
You can see above how the majority of the parameters to the tablediff command specify the source and destination (the location of the two tables to compare). The -f parameter specifies the filename of the output file that is generated that contains the SQL statements that can be run to 'synchronise' the data in the two tables.
Navigate to the file that you specified in the -f parameter above and when you open it you should see the following code which you can run if you wish from a query window in SSMS to update the CustomersBackup table so that it contains the same data as the Customers table.
The full syntax for the tablediff command is as follows:
For more details on the various parameters listed above see Microsoft technet
In this article we have demonstrated the purpose of the tablediff command line utitlity that is new to SQL Server 2005. We have demonstrated from scratch, a real world example of the command in use and then seen the full syntax of the command which shows many additional optional parameters.