Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Compare records from several tables

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2003-01-15 : 14:54:07
chris writes "I have a set of tables, tableA and tableB. They are identical in structure except tableB contains data which is 3 months old. I can join the tables using the primary key.

I want to look for any records that have changed. I can do something like this:

select * from tableA a, tableB b where a.primarykeyfield=b.primarykeyfield AND ((a.field1!=b.field1) OR
(a.field2 !=b.field2)) ...... up to field 10.

I have 30 sets of such tables, each set has 2 tables, and each set of 2 tables has very differenmt field names.

Is there any way I can quickly check for different records in each tableA, tableB set without having to hard-code for each of the 30 sets?"

SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-15 : 19:48:11
You might check some of the tools offered by Red-Gate Software. Not sure if anything they have will really do what you want, but it's a start.

The more you know, the more you know you don't know.
Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 20:14:44
First, the example you gave will have trouble with NULLS on the joins, so be careful of that.

For me, the easiest way to compare two tables to do a UNION ALL of both tables and GROUP BY all of the fields that should be the same. If the COUNT(*) is 1, there is not a match.

SELECT MAX(TableName) as TableName, a.Key
SELECT 'TableA' as TableName, a.Key, a.Field1, a.Field2 ...
FROM TableA a
SELECT 'TableB' as TableName, b.Key, b.Field1, b.Field2 ...
FROM TableB b
GROUP BY key, field1, field2 ... /* note: do not group by TableName */

The above will return the key field and the tablename of a record that is different or does not exist in either table, and handles NULL values with no problem.

Maybe something along those lines? Though writing SQL like this for every table could be quite time consuming. However, because you said the fieldnames are different in the tables, you will have to do quite alot of manual work anyway to compare the two tables.

Good luck.

- Jeff
Go to Top of Page

Starting Member

6 Posts

Posted - 2003-01-15 : 21:05:41
It should be possible to write a generic stored proc
"spReconcile(@Table1,@Table2)" that does the above (jsmiths grouped union thing) but dynamically constructs the column list using something like:

SELECT @Cols = @Cols + ',' + Column_Name
WHERE Table_Name = @Table1

...and I think you should be able to reuse the column list in both the selects and the group by (no need to give the two tables different aliases as they are unioned)

Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 21:48:30
Good idea, Bsmith.

I just posted:

I didn't generate the column list automatically, however; I figured in many cases you might want to pick the exact columns, or in the case of the above question, even the column names might not match. Also, I wanted it to work with views as well.

- Jeff
Go to Top of Page

- Advertisement -