SQL Server 2005 TableDiff Utility

March 29, 2006

One of the many command line utilities that came with SQL Server 2005 is the TableDiff utility. Although this utility is primarily designed for comparing replicated tables, we can take advantage of its functionality to compare tables. The objective of this article is to demonstrate the TableDiff utility.

Let us assume that we have the following databases and table.

Step 1

set quoted_identifier off
go
USE [master]
GO
/****** Object:  Database [MyDB1]    Script Date: 03/22/2006 08:08:48 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB1')
DROP DATABASE [MyDB1]
go
create database MyDB1
go
use MyDB1
go
create table ABC (id int primary key, name varchar(100),salary money)
go
insert into ABC select 1,"Catherine Donnel",200000
insert into ABC select 2,"Stacey Kost",150000
insert into ABC select 3,"Jason Leanos",36000
insert into ABC select 4,"Catherine O'Donnel",20000
go
USE [master]
GO
/****** Object:  Database [MyDB2]    Script Date: 03/22/2006 08:09:12 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB2')
DROP DATABASE [MyDB2]
go
Create database MyDB2
go
use MyDB2
go

create table ABC (id int primary key, name varchar(100),salary money)
go
insert into ABC select 1,"Catherine O'Donnel",220000
insert into ABC select 2,"Stacey Kostue",230000
insert into ABC select 4,"Catherine O'Donnel",20000
insert into ABC select 9,"Irina Zolotrova",40000
go

Step 2

Go to the command prompt [Click Start – Run – Cmd.exe – press enter] and type the following commands. [refer Fig 1.0]

Note: Change the path depending on your installation configuration. The TableDiff utility is installed under the

Program Files\Microsoft SQL Server\90\COM folder

C:\> cd "C:\Program Files\Microsoft SQL Server\90\COM"
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SQL2005" -
sourcedatabase "MyDB1" -sourcetable "ABC" -destinationserver "SQL2005" -destinat
iondatabase "MyDB2" -destinationtable "ABC"

Result

User-specified agent parameter values:
-c
-sourceserver SQL2005
-sourcedatabase MyDB1
-sourcetable ABC
-destinationserver SQL2005
-destinationdatabase MyDB2
-destinationtable ABC
Table [MyDB1].[dbo].[ABC] on SQL2005 and Table [MyDB2].[dbo].[ABC] 
 on SQL2005 have 4 differences.
Err     id      Col
Mismatch        1       name salary
Mismatch        2       name salary
Src. Only       3
Dest. Only      9
The requested operation took 0.3905616 seconds.

Step 3

Another cool function of the TableDiff utility is the generation of SQL Scripts for the differences that it found. Let us re-run the TableDiff utility by adding another –f parameter.

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SQL2005" -
sourcedatabase "MyDB1" -sourcetable "ABC" -destinationserver "SQL2005" -destinat
iondatabase "MyDB2" -destinationtable "ABC"  -f "c:\Diff"

Result

-sourceserver SQL2005
-sourcedatabase MyDB1
-sourcetable ABC
-destinationserver SQL2005
-destinationdatabase MyDB2
-destinationtable ABC
-f c:\Diff
Table [MyDB1].[dbo].[ABC] on SQL2005 and Table [MyDB2].[dbo].[ABC] on SQL2005 ha
ve 4 differences.
Fix SQL written to c:\Diff.sql.
Err     id      Col
Mismatch        1       name salary
Mismatch        2       name salary
Src. Only       3
Dest. Only      9
The requested operation took 0.3905616 seconds.

This time the TableDiff Utility also created C:\Diff.SQL file as shown below. [refer Fig 1.1]

-- Host: SQL2005
-- Database: [MyDB2]
-- Table: [dbo].[ABC]
UPDATE [dbo].[ABC] SET [name]='Catherine Donnel',[salary]=200000.0000 WHERE [id] = 1
UPDATE [dbo].[ABC] SET [name]='Stacey Kost',[salary]=150000.0000 WHERE [id] = 2
INSERT INTO [dbo].[ABC] ([id],[name],[salary]) VALUES (3,'Jason Leanos',36000.0000)
DELETE FROM [dbo].[ABC] WHERE [id] = 9

Other options can be used in the TableDiff Utility. They can be found by executing the following command:

C:\Program Files\Microsoft SQL Server\90\COM>tablediff /?

Conclusion

This article has demonstrated the major function of the TableDiff utility.

» See All Articles by Columnist MAK