SQL Server tablediff command line utility

By:   |   Comments (7)   |   Related: 1 | 2 | 3 | 4 | More > Comparison Data and Objects


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. 


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

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

 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 (36105)

many thanks for this article!!

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


In which database,  "Difference" table will create?

Monday, December 2, 2013 - 4:06:08 PM - Doug Back To Top (27658)

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 (22913)

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 (22911)

@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 (22908)

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?

get free sql tips
agree to terms