SQL Server tablediff Utility by serverku

Earlier, when I was working with task to sync data for two tables between two databases, I got the chance to use the tablediff.exe utility provided by SQL server. It used to compare data for two tables which have similar columns and data type structure. After comparing it generates transact SQL script log for discrepancies.

We can use with command line or with a batch file. Let us see how can we use with command line. This utility is found in “C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe” path or wherever the SQL server installed. tablediff.exe used to compare table data in same servers\databases or different servers\databases. The syntax to use it as following.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
    –sourceserver [SourceServer]
    -sourcedatabase [SourceDatabase]
    -sourceschema [SourceSchema]
    -sourcetable [SourceTable]
    -sourceuser [SourceUser]
    -sourcepassword [SourcePassword]
    -destinationserver [DestinationServer]
    -destinationdatabase [DestinationDatabase]
    -destinationschema [DestinationSchema]
    -destinationtable [DestinationTable]
    -destinationuser [DestinationUser]
    -destinationpassword [DestinationPassword]
    -et Difference
    -f [FullFilePath]

You can find other arguments and more specification here.   Let's go through with small testing, creating Domo objects and use in the example.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

USE DestDB
GO

CREATE TABLE dbo.DestObj
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

SELECT * 
FROM Sourcedb.dbo.SourceObj
SELECT * 
FROM Destdb.dbo.DestObj
GO

Now, turn on tablediff.exe and batch created with the following code.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
-sourceserver [DemoServer] 
-sourcedatabase [SourceDB] 
-sourceschema [dbo] 
-sourcetable [SourceObj] 
-sourceuser [sa] 
-sourcepassword [test@1234] 
-destinationserver [DemoServer] 
-destinationdatabase [DestDB]
-destinationschema [dbo]  
-destinationtable [DestObj] 
-destinationuser [sa] 
-destinationpassword [test@1234] 
-et Difference 
-f C:\DiffOutput

After creating a batch file with the above code and run it and the output resulted named “DiffOutput.sql” in C: drive which is in the form of the SQL which can be executed in SQL server. Following are the changes as described in the above image.

-- Host: DemoServer 
-- Database: [DestDB] 
-- Table: [dbo].[DestObj] 
UPDATE [dbo].[DestObj] SET [CreatedDate]='2012-09-29 04:11:34.820' WHERE [id] = 1 
INSERT INTO [dbo].[DestObj] ([CreatedDate],[id],[name]) VALUES ('2012-09-29 04:11:34.820',2,'test2') 
DELETE FROM [dbo].[DestObj] WHERE [id] = 3

You can see the changes generated, we have to apply to destination databases, so data will properly sync from source to destination. I will post the next topic to use tablediff.exe in size, and it will be with multiple tables. Please share your thought here to use in any other way.