SQL Server 2005 TableDiff utility

While testing the Replication in SQL 2005, I found one of the command line utilities that came with SQL Server 2005 named TableDiff. It's is primarily designed for comparing replicated tables, but we can take advantage of its functionality to compare tables in the same database or in different databases. The objective of this article is to demonstrate how we can take advantage of its functionality to compare tables.
Let us assume that we have a table named TEST1 in the database TESTDB1,and a table named TEST2 in the database TESTDB2,the both tables TEST1 and TEST2 having the same structure,we will use the tablediff utility to generate script that will make the data in TEST2 exactly the same as TEST1,we can do the reverse by switching the source and the destination options parameter of Tablediff utility
N.B: The unique key is important for the data comparison

set quoted_identifier off
go
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'TESTDB1')
DROP DATABASE [TESTDB1]
go
create database TESTDB1
go
use TESTDB1
go
create table TEST1 (id int primary key,
name varchar(100),
sal money)
go

ALTER TABLE TEST1 ADD UNIQUE
( [ID] ASC )
go

insert into TEST1 select 1,"Name1",1000
insert into TEST1 select 2,"Name2",2000
insert into TEST1 select 3,"Name3",3000
insert into TEST1 select 4,"Name4",4000
go
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'TESTDB2')
DROP DATABASE [TESTDB2]
go
Create database TESTDB2
go
use TESTDB2
go

create table TEST2 (id int primary key,
name varchar(100),
sal money)
go

ALTER TABLE TEST2 ADD UNIQUE
( [ID] ASC )
go

insert into TEST2 select 1,"Name1",1000
insert into TEST2 select 2,"Name2",2000
insert into TEST2 select 4,"Name4",5000
insert into TEST2 select 9,"Name9",9000
go

From to the command line type the following commands:
Type cd Type cd C:\Program Files\Microsoft SQL Server\90\COM
Than when the command prompt changed to
C:\Program Files\Microsoft SQL Server\90\COM>
type

tablediff -sourceserver "ABOUHMEID02\SQLEXPRESS" -sourcedatabase "TESTDB1" -sourcetable "TEST1" -sourceuser "sa" -sourcepassword "sa" -destinationserver "ABOUHMEID02\SQLEXPRESS" -destinationdatabase "TESTDB2" -destinationtable "TEST2" -destinationuser "sa" -destinationpassword "sa" -f "c:\compare.sql"

N.B: If you installed SQL SERVER 2005 in a directory different than
C:\ Program Files\Microsoft SQL Server\90\COM folder
Change the path depending on your installation configuration.
The TableDiff utility is installed by default under
C:\Program Files\Microsoft SQL Server\90\COM folder

Result
User-specified agent parameter values:
-sourceserver ABOUHMEID02\SQLEXPRESS
-sourcedatabase TESTDB1
-sourcetable TEST1
-sourceuser sa
-sourcepassword sa
-destinationserver ABOUHMEID02\SQLEXPRESS
-destinationdatabase TESTDB2
-destinationtable TEST2
-destinationuser sa
-destinationpassword sa
-f c:\compare.sql

Table [TESTDB1].[dbo].[TEST1] on ABOUHMEID02\SQLEXPRESS and Table [TESTDB2].[dbo].[TEST2] on ABOUHMEID02\SQLEXPRESS have 3 differences.
Fix SQL written to c:\compare.sql.
Err id Col
Src. Only 3
Mismatch 4 sal
Dest. Only 9
The requested operation took 0.40625 seconds.

The generated sql file C:\Compare.SQL file :

-- Host: ABOUHMEID02\SQLEXPRESS
-- Database: [TESTDB2]
-- Table: [dbo].[TEST2]
INSERT INTO [dbo].[TEST2] ([id],[name],[sal]) VALUES (3,'Name3',3000.0000)
UPDATE [dbo].[TEST2] SET [sal]=4000.0000 WHERE [id] = 4
DELETE FROM [dbo].[TEST2] WHERE [id] = 9