Comparing Tables Again - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Comparing Tables Again

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-12-28 : 21:09:58

I need to compare two tables and update the records that have changed.
The following code works great for that:
SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

However, I also need to compare the two tables and see if there are any new records or deleted records. What would those queries look like? Can they all somehow be combined into one query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-29 : 08:22:37

Assuming you are comparing Table A to B,the original table

SELECT *
FROM TableA a
LEFT OUTER JOIN TableB b
ON b.ID=a.ID
WHERE b.ID IS NULL

gives those records in A which is not present in B (new records inserted)

Similarly

SELECT *
FROM TableB b
LEFT OUTER JOIN TableA a
ON a.ID=b.ID
WHERE a.ID IS NULL

gives those records in B which is not present in A (the records which were deleted)

You acn combine these two with the current query using UNION ALL

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-12-29 : 11:40:04

That's it! Thanks so much. Now how do I combine the two in the current query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-29 : 11:50:15

What should be your end result?

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-12-29 : 12:32:42

I'm wondering if it's not best just to have multiple queries instead of just one query. One each for changes, additions, and deletions. That way I can see the deleted records and process that info accordingly and see the new records and process that info differently. I guess we could characterize the records as deleted, added, or changed within the query, but I'm wondering if all of this processing would slow down the system too much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-29 : 12:40:26

i think you will need three categories:-

1.Delete part deleting records in main table but not in new table.

2.Update part which updates those records in original table whose values have chaged since then

3.Insert part which inserts new records (that which are not present in original table)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources