By: Greg Robidoux | 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.
When the command is run this is the output that you get:
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.
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.
This is the output we get from the file that is created "c:\table1_differences.sql"
-- 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.
About the author
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
