Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server tablediff command line utility


By:   |   Updated: 2006-09-25   |   Comments (7)   |   Related: 1 | 2 | 3 | 4 | More > Comparison Data and Objects

Problem

One problem that DBAs often face is maintaining lookups tables across multiple servers or sites.  These tables could either be replicated or manually updated, but in any case sometimes the data in these tables get out of synch.  In a previous tip we talked about SQL Server comparison tools that allowed you to compare database objects or even the data itself between two different databases.  These tools are great and definitely offer a lot of advantages,  but there is a tool in SQL Server that might help as well. 

Solution

SQL Server comes with many GUI and command line tools and one new tool that is available is the tablediff command.  This utility allows you to compare the contents of one table against another table to identity if there are differences as well as create the T-SQL commands to get the tables back in synch.

The one problem with this command is that it is a command line tool, there is not GUI so writing the command is a bit tedious.  You can create a batch file and execute the batch file, but still it would be nice if there were a GUI.

The command takes a few basic parameters to run.

  • sourceserver
  • sourcedatabase
  • sourcetable
  • destinationserver
  • destinationdatabase
  • destinationtable

Here is an example command that compares two tables Table1 and Table2 in the same database.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1 -sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test -destinationtable table2

When the command is run this is the output that you get:

Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.

User-specified agent parameter values:
-sourceserver server1
-sourcedatabase test
-sourcetable table1
-destinationserver server2
-destinationdatabase test
-destinationtable table2

Table [test].[dbo].[table1] on server1 and Table [test].[dbo].[table2] on server1 have 3 differences.
Err PersonID
Mismatch 1
Dest. Only 2
Src. Only 3
The requested operation took 0.4375 seconds.

From this basic command we can see there are differences, but it is not very helpful as to what the problem is, so to make this more useful we can use the "-et" argument to see the differences in a table.  The "et" parameter will create a table, in our case called "Difference", so we can see the differences in a table.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1  -sourcedatabase test -sourcetable table1 -destinationserver server1  -destinationdatabase test -destinationtable table2 -et Difference

When we query the table "Difference" that was created we see the following results.  As you can see this is not any more helpful then the first run.

PersonId MSdifftool_ErrorCode MSdifftool_ErrorDescription
1 0 Mismatch
2 1 Dest. Only
3 2 Src. Only
NULL NULL NULL

Another option is to use the "-f" argument that will create a T-SQL script to synchronize the two tables.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver server1  -sourcedatabase test -sourcetable table1 -destinationserver server1 -destinationdatabase test -destinationtable table2 -et Difference -f c:\table1_differences.sql

This is the output we get from the file that is created "c:\table1_differences.sql"

-- Host: server1
-- Database: [test]
-- Table: [dbo].[table2]
UPDATE [dbo].[table2] SET [LastName]=NULL WHERE [PersonID] = 1
DELETE FROM [dbo].[table2] WHERE [PersonID] = 2
INSERT INTO [dbo].[table2] ([FirstName],[LastName],[PersonID]) VALUES ('Bob','Jones',3)

From here we can see the exact differences as well as having a script that we can run against Table2 to make the contents identical to Table1.

Next Steps
  • This is a brief introduction of this new tool and how it can be used, check out tablediff in books online for additional options
  • Add this new tool to your SQL toolbox and deploy where you need to keep certain tables in synch.
  • In order to run this command you need to have primary keys setup on both tables.


Last Updated: 2006-09-25


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 28, 2016 - 1:41:35 AM - rohit chati Back To Top

 hi Can this be used to compare the data between two databases post upgrade. Eg. if one database is on SqlServer 2005 on an old windows platform and we upgrade this to SqlServer2008 on Say Windows 2008 can we use this command tablediff.exe

 


Friday, January 30, 2015 - 12:54:32 AM - sql010 Back To Top

many thanks for this article!!


Thursday, February 13, 2014 - 11:41:51 AM - Sree Back To Top

 

In which database,  "Difference" table will create?


Monday, December 02, 2013 - 4:06:08 PM - Doug Back To Top

Hi Greg

 

I like the utility but not sure if it is the thing I am looking for. I have a project where I am trying to match records in two tables in separate databases based on criteria such as Name, Address, Phone, etc. The problem is that the data was manually entered in each database with no real constraints or naming convention so you might have a name like “Sunrise Optical” in table1 and “Sunrise Optical Solutions Inc.” in another. In most cases the difference is even more extreme than that. Do you know a utility that can compare and match data in this case?


Wednesday, March 20, 2013 - 1:57:17 PM - Greg Emery Back To Top

Thanks Greg.  That makes complete sense.  Ang thanks for pointing out this free tool.  I'll definitely use it for my QA testing in the future. 


Wednesday, March 20, 2013 - 12:54:14 PM - Greg Robidoux Back To Top

@Greg Emery - Hi Greg, I agree that this is probably not the most efficient way of doing this.  The best way is to probalby use a third party tool, but I wanted to show a free tool that does allow you to do this as well.

You could use Replication to synch two data sets.  If data is out of balance you would need to select one of the datasets as the master and then you could refresh the other dataset.  If there some data that only exists on Server A and other data that exists on Server B you would need another way to figure out the differnces in order to not lose any data if that was the desired end result.


Wednesday, March 20, 2013 - 11:01:54 AM - Greg Emery Back To Top

This is a very good article.  I could see using this when performing testing and comparing an older import/update process with a newer one.  But in a production type environment, wouldn't a Publication/Subscription process prove to be more reliable and less manual than the tablediff.exe?  If not, can you explain why?


Learn more about SQL Server tools