By: Greg Robidoux | Updated: 2006-09-25 | 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.
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:
Table [test].[dbo].[table1] on server1 and Table [test].[dbo].[table2] on server1 have 3 differences.
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.
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.
- 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
About the author
View all my tips