Compare data in 2 tables - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Compare data in 2 tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-05 : 10:57:30

Katarina writes "I've been trying to find out for long of the best way to compare data in 2 tables with exactly the same structure, ie. the two tables have the same columns, data types and everything else, just different data in them. I would like to pull out only those rows that don't appear in both tables, ie. the difference.
Thanking you in advance
Katarina"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-05 : 11:11:35

What you are talking about is the Relational Algegra concept of 'Difference'....


select * 
    from TABLE_A
    where A_KEY not in (select A_KEY from TABLE_B)

if you are working with a SQL-92 compliant product, the EXCEPT keyword works nicely and follows the same rules as a UNION...


select * 
    from TABLE_A
except
select * 
    from TABLE_B

<O>

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-05 : 12:10:03

you might also look at software from red-gate.com.....they have stuff which can compare 2 tables....both in structure and in values.

Katarina
Starting Member

6 Posts

Posted - 2002-07-08 : 10:48:28

This is okay, but I want to compare also other columns which are not primary keys in my two tables (the two tables are exactly of the same structure), ie. I need to compare ALL columns within rows with the same primary keys in these two tables.

quote:

What you are talking about is the Relational Algegra concept of 'Difference'....

select *
from TABLE_A
where A_KEY not in (select A_KEY from TABLE_B)

if you are working with a SQL-92 compliant product, the EXCEPT keyword works nicely and follows the same rules as a UNION...

select *
from TABLE_A
except
select *
from TABLE_B

<O>

MakeYourDaddyProud

184 Posts

Posted - 2002-07-08 : 11:14:16

There are plenty of resources on the net about sequential matching algorithms, which tend not to be set based and if you are erring on the side of a cursor solution, the algos will slot nicely. Page's makes a reference to a set based 'extract' logic which might satisfy your requirement.

Take a look at RIGHT OUTER JOIN and link that to a distinct list of all primary keys amalgamated from both tables. Null joins would isolate those rows not present in each table over the keyed join.

CREATE VIEW
as

SELECT DISTINCT key
FROM (SELECT pri_key FROM TABLEA
UNION ALL
SELECT pri_key FROM TABLEB) as TableAB

RIGHT OUTER JOIN the above to Both TableA and TableB again to isolate missing keys.

HTH

Daniel Small MIAP
www.danielsmall.com IT Factoring

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources