Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

TableDiff.exe - a cool SQL Server 2005 tool

While searching for something in BOL i've accidently stumbled onto this little cool command line utility

TableDiff.exe is a table comparison tool that comes with the sql server.

It's installed on the server in the:

"C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe"

if your SQL Server is installed in the program files on c: drive.

 

Example use:

This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences:

 

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
                                                             -sourcedatabase MyDatabase1
                                                             -sourcetable MyTable1
                                                             -destinationserver MyServer1
-destinationdatabase MyDatabase1 -destinationtable MyTable2 -et DiffsTable

 

This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences and

creates a T-SQL script file at d:\MyTable1_MyTable2_diff.sql

that holds the UPDATE/INSERT/DELETE statements to synchronize the 2 tables:

 

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
-sourcedatabase MyDatabase1 -sourcetable MyTable1 -destinationserver MyServer1
-destinationdatabase MyDatabase1 -destinationtable MyTable2 -et DiffsTable -f d:\MyTable1_MyTable2_diff.sql

 

More info on this VERY COOL little tool in BOL here.

UPDATE: I've created a GUI for this tool. Look here.

 

kick it on DotNetKicks.com

Legacy Comments


Ryan Smith
2007-03-05
re: TableDiff.exe - a cool SQL Server 2005 tool
Wow, that sure would have helped me out last Friday when I was trying to sync up two databases that were running on different servers.

Thanks for informing me of this tool.

Mladen
2007-03-05
re: TableDiff.exe - a cool SQL Server 2005 tool
you're welcome
look around. I have my moments when i'm full of wisdom :))

Rolf Thomassen
2007-03-14
re: TableDiff.exe - a cool SQL Server 2005 tool
Thanks for this info...

Does it only work for SQL 2005 or can it also test against SQL 2000 ?
I need a tool for this ..
At least until I can migrade my servers to SQL2K5


Mladen
2007-03-14
re: TableDiff.exe - a cool SQL Server 2005 tool
haven't tested it for SS2k.

SDolgin
2007-03-14
re: TableDiff.exe - a cool SQL Server 2005 tool
I can confirm this is working with SQL2000 database instances just fine. I am using it to script seed data from source tables to be used during new deployments of our application.

Mladen
2007-03-14
re: TableDiff.exe - a cool SQL Server 2005 tool
cool!

Thanx

shravan
2007-05-10
re: TableDiff.exe - a cool SQL Server 2005 tool
How to get the values from two different tables belong to two different databases

Mano Bharath
2007-07-02
re: TableDiff.exe - a cool SQL Server 2005 tool
How do i get the output values from the Tablediff.exe Ex: 0 - Success, 1 - Critical error, 2 - Table differences
in VS2005, C#

Raghu
2007-07-16
re: TableDiff.exe - a cool SQL Server 2005 tool
Its Cool.................................

Ditch AKA Duane
2007-08-14
re: TableDiff.exe - a cool SQL Server 2005 tool
Hey Spirit - this is sooo cool.
Are you making use of EXCEPT?

That pretty much returns whats in one datasource and not the other, so it can be used both ways eg

insert into OnANotB
select * from A
except
select * from B

insert into OnBNotA
select * from B
except
select * from A

as far as I know this is only available on 2005 though.

Mladen
2007-08-14
re: TableDiff.exe - a cool SQL Server 2005 tool
i'm not using anything :)

TableDiff is a tool that comes WITH sql server. I didin't create it :)
it's used by SQL Server for finding differences in replication.

vinod
2007-08-20
re: TableDiff.exe - a cool SQL Server 2005 tool
this tool is not working on my machine...............

actually i didn't installed it....directly i clicked on the above link....
i'm using windows 2000 server and i've client version in my machine....
i've both 2000 and 2005 ... client versions

Mladen
2007-08-20
re: TableDiff.exe - a cool SQL Server 2005 tool
there's nothing to install. just run the exe.
however note that the tablediff.exe is located on the SQL Server computer, NOT on the client computer.

so either copy the tablediff.exe to your client or use the tool on the server machine.

Bigpa
2007-11-07
re: TableDiff.exe - a cool SQL Server 2005 tool
Cool tool, skills

Janani
2008-01-17
re: TableDiff.exe - a cool SQL Server 2005 tool
How do we implement the same in SSIS. believe there is a tablediff tool in SSIS. How do i access the tool? Someone kindly throw some light on this.
Thanks in advance

Mladen
2008-01-17
re: TableDiff.exe - a cool SQL Server 2005 tool
no idea on how to do this in ssis.

steve
2008-01-31
re: TableDiff.exe - a cool SQL Server 2005 tool
i like your gui, but i consider the tablediff tool fairly useless. if i want to compare my tables one at a time, i can just eyeball it, thanks for nothing MS.

Karthikeyan Kannan
2008-02-06
re: TableDiff.exe - a cool SQL Server 2005 tool
Thanks & Really this tool is a gud one, but i am not able to generate the SQL Syn Scripts using the tool (as well as in command prompt). Can any one help me out in generating the differential scripts using this tool? reply me asap...

luci
2008-10-30
re: TableDiff.exe - a cool SQL Server 2005 tool
do you know...is there a way to find the scripts that do "the job" for TableDiff?

Mladen
2008-10-30
re: TableDiff.exe - a cool SQL Server 2005 tool
fire up the profiler and see what tablediff is doing. my guess is that the difference script is built inside and not with SQL

Bryan
2009-04-30
re: TableDiff.exe - a cool SQL Server 2005 tool
That is a nice little GUI. Thank you for sharing it!

Jack Shaftoe
2009-05-15
re: TableDiff.exe - a cool SQL Server 2005 tool
> i consider the tablediff tool fairly useless. if i want to compare my tables one at a time, i can just eyeball it

Man, you must have lots of free time. Try eyeballing tables with a few million records.

Thanks for the utility Mladen.

andriy
2009-11-08
re: TableDiff.exe - a cool SQL Server 2005 tool
hi
when you say differences, do you mean rows from a source table that are not in a destination table, or vs, or even both?
thanks

Ram Lal
2009-12-03
re: TableDiff.exe - a cool SQL Server 2005 tool
Hi, Thanks for sharing this great tool !! Is it possible to compare the whole DB tables or multiple at one go and generate related scripts? Any help would be appreciated. Thank you

Mladen
2009-12-03
re: TableDiff.exe - a cool SQL Server 2005 tool
no. it's not.

Wayne Bloss
2010-07-16
re: TableDiff.exe - a cool SQL Server 2005 tool
I a that does bulk processing using tablediff.exe.

You can find the source code and binary release here - http://code.google.com/p/sqltablediff/

ayhan
2010-08-09
re: TableDiff.exe - a cool SQL Server 2005 tool
Can you send me tablediff.exe source code

MAR
2010-10-11
re: TableDiff.exe - a cool SQL Server 2005 tool
TableDiff.exe - a cool SQL Server 2005