Compare records from several tables

Please start any new threads on our new site at https://forums.sqlteam.com. 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

AskSQLTeam
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?"

AjarnMark
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.

jsmith8858
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
FROM
(
SELECT 'TableA' as TableName, a.Key, a.Field1, a.Field2 ...
FROM TableA a
UNION ALL
SELECT 'TableB' as TableName, b.Key, b.Field1, b.Field2 ...
FROM TableB b
)
GROUP BY key, field1, field2 ... /* note: do not group by TableName */
HAVING COUNT(*) = 1

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

BSmith
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
FROM INFORMARTION_SCHEMA.Column.
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)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 21:48:30

Good idea, Bsmith.

I just posted:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources