We have to do this kind of compare on a regular basis to test the results of complex algorithms in our application software. We also have recovery situations where we selectively restore data.

There are two methodologies that we use.

1. We unload one database's tables using the unload statement from SQL Central - this with the unload method that creates the tables on reload. We then edit the table names to be recreated on the load (e.g. table name > tablename_test) We then execute the load script which populates the test data in the database. We can then do various NOT EXISTS queries or variation compares on column values for matching keys between tablename and tablename_test. Note that you can you can use the MERGE Statement to insert rows from one into the other based on conditional tests (MERGE is a fantastic tool for this stuff and modifying the data in the source (insert) tables gives you a lot of control over the final result). I note that in your case you might be going from production to test - we have had to do a similar process when we needed to selectively restore information from a historical backup.

2. For prolonged complicated sets with lots of columns (some containing text) we export the tables into two excel spreadsheets (use the new version of excel that can handle a large number of rows). We then use an Excel Compare software that does a fantastic job of identifying and highlighting differences - including Inserts and Deletes.

You can use the UNLOAD SELECT statement to output the result of a query which (presumably) will join all of the necessary tables so that the output is a single file.

This approach will work well only if you have a star-schema (or something close) that will limit the size of the output file and, more importantly, make it easier to determine where the missing/additional data is coming from. My point is that if you're joining ten tables, and then compare the result from two databases, it can be considerably harder to pinpoint the missing/extra data if the output file differs by tens or hundreds of thousands of rows - all because 5 rows were missing from a single table.

Another way of doing the comparison is to set up proxy tables to the other database, and then compose a FULL OUTER JOIN between the like tables in each database. This avoids the need to export the database contents to a flat file, but will not perform as well for large datasets.