Sql Server 2005 Database Tables - Row Comparison Column By Column.

Question

Scenario

I have an TWO datbase tables of exactly the SAME STRUCTURE. The difference between these tables is that one contains data populated by one application and the other is populated by a different application. Each application is trying to produce the same result, but using two different methods of implementation.

Proposed Idea

What I want to do, is run both applications, which will roughly produce 35000 rows containing 10 columns each - So all in all, 70000 rows of data, I then want to compare each row of data, COLUMN BY COLUMN to check whether the values are the same or not.

Current Thoughts

Since there is so much data to compare, I feel that the best way in which to do this would be to write an application, preferably in C# (but if necessary, T-sql), to compare each row of data column by column, and write out any failed comparisons to a text log file.

Question

Could anybody suggest an efficient way in which to perform column by column row comparison for 70000 rows worth of data? I'm struggling for ideas on how to tackle this problem.

Extra Detail

The two applications are both written in C# .Net 3.5. The Database is running on Sql Server 2005.

Help greatly appreciated.

Answer 1

There is already a product from Red Gate called SQL Data Compare that can show you differences between two tables in an SQL Server database. It will save you some work and has a nice visual display of the results. It's not free, but you can try a trial version before you buy.

I have used this product before with good results.

Answer 2

Have you tried a MINUS query? This will return all rows from the first table that are not in the second or vice versa...

Answer 3

I disagree with your statement:

Since there is so much data to compare, I feel that the best way in which to do this would be to write an application, preferably in C#

SQL Server is designed to efficiently perform set operations on large amounts of data, C# is not. You could create a query to compare the tables on all columns and write the differences to a result table; have a look at the EXCEPT operator.

You don't say how you would resolve any differences, would it require a manual check of the differences or do you intend to do it automatically?

Actually, if you are only going to do the check manually there are a number of tools to do this sort of comparison for you, here's a reasonably priced one: http://www.datanamic.com/datadiff/index.html