TableDiff Command-Line Utility

Tablediff is an SQLCMD command utility that allows you to compare the data contained within two different tables. This was developed for replication when data in two tables should be the same.This tool can determine if the content of tables involve in replication are the same, and if they are different, it can identify what data in the tables are different.

Let me demostrate this with this simple example:

USE AdventureWorks
GO
Create table table1( id int identity(1,1) NOT NULL, name varchar(50) )GO
Create table table2( id int identity(1,1) NOT NULL, name varchar(50) )GO
insert into table1 values('Jesse')
insert into table1 values('Emmanuela')
GO
insert into table2 values('Jesse')
insert into table2 values('Emma')
GO
--Now run the tablediff util on the command line from this folder:C:\program files\microsoft sql server\90\com\tablediff.exe

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "pavilion" -sourcedatabase "AdventureWorks" -sourceschema "dbo" -sourcetable "table1" -destinationserver "pavilion\sql2005" -destinationdatabase "AdventureWorks" -destinationschema "dbo" -destinationtable "table2" -f c:\Tablediff_output.txt

-- The execution of the utility with the above parameters results in the following output to the command prompt window:

User-specified agent parameter values:
-sourceserver pavilion\sql2005
-sourcedatabase AdventureWorks
-sourceschema dbo-sourcetable table1
-destinationserver pavilion\sql2005
-destinationdatabase AdventureWorks
-destinationschema dbo
-destinationtable table2
-f c:\Tablediff_output.txt

Table [AdventureWorks].[dbo].[table1] on pavilion\sql2005 and Table [AdventureWorks].[dbo].[table2] on pavilion\sql2005 have 1 differences.Fix SQL written to c:\Tablediff_output.txt.sql.

Err id Col
Mismatch 2 name
The requested operation took 1.46875 seconds.

The output displays the summary and shows the comparison results. It found one difference between table1 and table2. The -f parameter caused tablediff utility to output a SQL file that can be used to fix the difference in the destination file.

The output of the SQL file is:

-- Host: pavilion\sql2005
-- Database: [AdventureWorks]
-- Table: [dbo].[table2]
SET IDENTITY_INSERT [dbo].[table2] ON
UPDATE [dbo].[table2]
SET [name]='Emmanuela' WHERE [id] = 2
SET IDENTITY_INSERT [dbo].[table2] OFF