SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Compare records from several tables
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/15/2003 :  14:54:07  Show Profile  Visit AskSQLTeam's Homepage
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

USA
3246 Posts

Posted - 01/15/2003 :  19:48:11  Show Profile  Visit AjarnMark's Homepage
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

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/15/2003 :  20:14:44  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page

BSmith
Starting Member

New Zealand
6 Posts

Posted - 01/15/2003 :  21:05:41  Show Profile
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)

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/15/2003 :  21:48:30  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000