I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

Print | posted on Saturday, March 03, 2007 4:47 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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.
3/5/2007 9:33 PM | Ryan Smith
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

you're welcome
look around. I have my moments when i'm full of wisdom :))
3/5/2007 9:43 PM | Mladen
Gravatar

# 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

3/14/2007 12:36 PM | Rolf Thomassen
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

haven't tested it for SS2k.
3/14/2007 12:40 PM | Mladen
Gravatar

# 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.
3/14/2007 4:55 PM | SDolgin
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

cool!

Thanx
3/14/2007 5:02 PM | Mladen
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

How to get the values from two different tables belong to two different databases
5/10/2007 11:03 AM | shravan
Gravatar

# 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#
7/2/2007 4:02 PM | Mano Bharath
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

Its Cool.................................
7/16/2007 8:34 AM | Raghu
Gravatar

# 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.
8/14/2007 11:49 AM | Ditch AKA Duane
Gravatar

# 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.
8/14/2007 12:07 PM | Mladen
Gravatar

# 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
8/20/2007 6:27 AM | vinod
Gravatar

# 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.
8/20/2007 10:51 AM | Mladen
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

Cool tool, skills
11/7/2007 10:43 PM | Bigpa
Gravatar

# 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
1/17/2008 10:33 AM | Janani
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

no idea on how to do this in ssis.
1/17/2008 11:26 AM | Mladen
Gravatar

# 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.
1/31/2008 2:39 PM | steve
Gravatar

# 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...
2/6/2008 8:00 AM | Karthikeyan Kannan
Gravatar

# 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?
10/30/2008 1:58 PM | luci
Gravatar

# 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
10/30/2008 2:14 PM | Mladen
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

That is a nice little GUI. Thank you for sharing it!
4/30/2009 5:00 PM | Bryan
Gravatar

# 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.
5/15/2009 4:01 PM | Jack Shaftoe
Gravatar

# 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
11/8/2009 9:40 PM | andriy
Gravatar

# 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
12/3/2009 12:35 PM | Ram Lal
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

no. it's not.
12/3/2009 1:32 PM | Mladen
Gravatar

# 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/
7/16/2010 5:11 PM | Wayne Bloss
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

Can you send me tablediff.exe source code
8/9/2010 3:17 PM | ayhan
Gravatar

# re: TableDiff.exe - a cool SQL Server 2005 tool

TableDiff.exe - a cool SQL Server 2005
10/11/2010 5:42 PM | MAR
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET