Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to find a good open source tool that simply compares data in a database on one server to a table (of the same schema) on another server. Then I want to generate INSERT statements for the rows that are different, for either the source or destination/target table.

I've tried downloading several tools, and I can't get any of them to work because they all had bugs. I can be more specific with the errors I received if you need me to be.

I've tried:

TableDiffGui.zip
SqlDiffFramework setup 1.0.3.0.exe
SqlTableCompare.zip
DaBCoS.0.3.2163.zip

If there is a manual way to do this, please let me know. My last resort will be to simply write a program that does it.

share|improve this question
Do you really want to insert all of the rows that are different? Even if the key is the same and/or all of the columns are the same except one? Seems like what you might be looking for is MERGE if you're trying to keep two tables in sync (without using replication / mirroring / etc.). – Aaron Bertrand Jan 10 at 18:39

2 Answers

up vote 3 down vote

Is creating a linked server on one of those an option? If so you could probably do this using something similar to the following query (untested):

INSERT INTO Server1.db1.dbo.Table1
SELECT * FROM dbo.Table2
EXCEPT
SELECT * FROM Server1.db1.dbo.Table1

If not, then maybe take a look at SQL Server replication

share|improve this answer
1  
Nice Query +! could also take a look at RedGate for that however it's certainly not free. – Zane Jan 10 at 17:17
I'm checking with our DBAs if I can get permissions to add linked servers. Good thought of doing a federated query. – MacGyver Jan 10 at 18:26
up vote 1 down vote

I see that you found that TableDiffGui has bugs. Have you tried using TableDiff directly? You might not run into the same problems.

share|improve this answer
no, I have not tried that yet. – MacGyver Jan 10 at 18:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.