TableDiff.exe – Compare two tables and generate insert/update/delete’s

Update (Jan 25, 2017): This is an old post. The new method of doing data compares is by using SQL Server Data Tools (SSDT). The procedure is outlined here. TableDiff is still a great command-line, no-frills option.

If you interested in only finding out what rows were INSERTED/UPDATED/DELETED and have no need to generate any SQL based on the differences, please checkout my blog post on how to do this.

There is help in the form of a hidden tool named “TableDiff.exe” that comes as part of SQL Server. This intent of the .exe is to help with replication but nothing will stop you and me from using it on a day-to-day basis.  Given the source server/database/table and destination server/database/table, it compares and either creates a new table with differences or generates a sync-script that has INSERTS/UPDATES/DELETES as necessary to make the source and destination data equal.

The .exe is not in the DOS path so you would have to find it but for my version of SQL Server, it was at

C:\Program Files\Microsoft SQL Server\90\COM>

If you don’t have it at the above location, you can locate it by using

> dir /S tablediff.exe

TableDiff Syntax

The only quirk is that it is a command-line tool with a rather wordy syntax (shown in image above).

Even a very simple compare would look something like this (names have been changed to protect the innocent):

C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe
    -sourceserver "myserver\dev"
    -sourcedatabase "AdventureWorks"
    -sourceschema "dbo"
    -sourcetable "OrderHeader"
    -destinationserver "myserver\qa"
    -destinationdatabase "AdventureWorks"
    -destinationschema "dbo"
    -destinationtable "OrderHeader"
    -destinationuser "SQLServUser"
    -destinationpassword "DestPassword"
    -dt
    -f "C:\WINDOWS\Profiles\mine\Desktop\~TableDIff.sql"

Above, I used my integrated security login for the source and SQL Server user name and password for my destination with the table differences going to a custom file ~TableDiff.sql. It generated INSERTS and UPDATES to equalize the source and destination tables (altered version of output included here)

-- Host: mysqlinstance\qa
-- Database: [MyDBName]
-- Table: [dbo].[MY_TABLE]
INSERT INTO [dbo].[MY_TABLE] ([KEY_CODE],[MY_TABLE_CODE],[CREATE_DATETIME],[CLIENT]) VALUES ('CA','ADD','2010-11-09 18:05:50.220',2)
INSERT INTO [dbo].[MY_TABLE] ([KEY_CODE],[MY_TABLE_CODE],[CREATE_DATETIME],[CLIENT]) VALUES ('MA','ADD','2010-11-09 18:05:50.210',2)
UPDATE [dbo].[MY_TABLE] SET [CREATE_DATETIME]='2010-11-29 14:06:20.147' WHERE [KEY_CODE] = 'FDD' AND [MY_TABLE_CODE] = 'ADD' AND [CLIENT] = 3
UPDATE [dbo].[MY_TABLE] SET [CREATE_DATETIME]='2010-11-29 14:06:20.147' WHERE [KEY_CODE] = 'MDD' AND [MY_TABLE_CODE] = 'ADD' AND [CLIENT] = 3
UPDATE [dbo].[MY_TABLE] SET [CREATE_DATETIME]='2010-11-29 14:06:20.147' WHERE [KEY_CODE] = 'SDD' AND [MY_TABLE_CODE] = 'ADD' AND [CLIENT] = 3
UPDATE [dbo].[MY_TABLE] SET [CREATE_DATETIME]='2010-11-29 14:06:20.150' WHERE [KEY_CODE] = 'FHM' AND [MY_TABLE_CODE] = 'AHI' AND [CLIENT] = 3
UPDATE [dbo].[MY_TABLE] SET [CREATE_DATETIME]='2010-11-29 14:06:20.147' WHERE [KEY_CODE] = 'MHM' AND [MY_TABLE_CODE] = 'AHI' AND [CLIENT] = 3
UPDATE [dbo].[MY_TABLE] SET [CREATE_DATETIME]='2010-11-29 14:06:20.150' WHERE [KEY_CODE] = 'SHM' AND [MY_TABLE_CODE] = 'AHI' AND [CLIENT] = 3

“Neat”, you would say.  I am not done yet. Digging online, I found that Mladen Prajdic has wrapped a GUI around the tool to make it really a child’s play to use. You can download it from here

This is screen-shot of the tool in actionTableDiff GUI Wrapper

There are a lot of commerical tools that would do a lot better with more fine-grained as well as wholesome options but this is free and it is already on your machine if you have SQL Server installed on it!

If you want to reset the next identity value served by a table, you can check current value using

DBCC CHECKIDENT (‘tablename’, NORESEED)

and reset, using

DBCC CHECKIDENT ('tablename', RESEED, 10000)
Advertisements

One thought on “TableDiff.exe – Compare two tables and generate insert/update/delete’s

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s