compare data in 2 SQL tables

Question

As part of a data regression and quality assurance exercise, I need to look for diffs between two tables that should, ideally, be nearly identical. I have looked at a large number of commercial products and can't seem to find one that satisfies all of our requirements:

• Must be able to compare LARGE tables (10 million rows, by 200 columns) very efficiently

• Must work across different DB servers and different DB vendors (Oracle vs. DB2)

• Must be able to compare tables having different structures, ignoring the columns that are not shared between the two tables

• Must work with a user supplied, multi-column primary key-- can't rely on key defined in DB

• Must run on linux/solaris. Will be run as part of a fully automated process that is executed within an enterprise environment.

• Must be able to run headless (wihtout GUI)

• Must produce formatted report that identifies row diffs (row on only one side) and value diffs

• Customer is willing to pay enterprise level price for right solution. In other words, price no object.

Has anyone ever seen something like this?

Answer 1

Don't know if it's related, by have a look at http://stackoverflow.com/questions/847434/diffing-objects-from-a-relational-database

Answer 2

I'd hash the DB rows based on your defined criteria and then use that. If the comparison details are fairly static you may want to persist the hash, either as a new column in the table itself or in a separate dedicated table. An appropriate index would then allow you to perform whatever comparisons you wish.

Answer 3

select 'Table1' as tblName, * from (select * from Table1 except select * from Table2) x union all select 'Table2' as tblName, * from (select * Table2 except select * from Table1) x

Also, There are commercially available tools which are designed for this

Red Gate do one for £245, there must be 'freeware' I'd guess

http://www.red-gate.com/products/SQL_Compare/index.htm

Answer 4

Not the best solutions but for flexibility, we have implemented this as a set of perl scripts that extract the data and then do file comparison.

Most commercial databases have excellent bulk copy utility (bcp, sqlload etc.) and Perl is fast with string comparison and for proecessing large files.

Answer 5

Over the years, I have developed certain scripts that almost exactly match your requirements. I could productize them, i.e. make them easier to use with some additional effort.

Please contact me at androknego at yahoo dot com (no spaces, replace at with @ and dot with .)

Answer 6

Only runs on Windows but satisfies all your other requirements Zidsoft CompareData http://www.zidsoft.com/

Answer 7

A tool that meets all of your criteria now exists. It's free, it's Open Source, and it's very well documented, and it's a high-quality, well tested product.

http://www.diffkit.org