Reputation: 1769
I'm designing a dynamic SQL-query that will check a source-table against a target table. I would like to compare the rows of the source against the rows of the target to check for differences.
CREATE TABLE TABLE_A (KEY INT, COL1 INT, COL2 INT)
CREATE TABLE TABLE_B (KEY INT, COL1 INT, COL2 INT)
So i created this statement:
SELECT A.* FROM TABLE_A A
INNER JOIN TABLE_B B
ON B.KEY = A.KEY
AND (B.COL1<>A.COL1 OR B.COL2<>A.COL2)
But this only works as long as the values of col1 and col2 are not nulls. If table-a col1 is null and table-b col1 is null then i would consider them equal.
I know i can put an ISNULL around my columns, but this is a dynamic query being built up, so i only know the column name not the datatype.
Any suggestions?
Upvotes: 5
Views: 179
Reputation: 6205
If TABLE_A and TABLE_B have same columns. You may try this
SELECT * FROM TABLE_A
EXCEPT
SELECT * FROM TABLE_B
Upvotes: 0
Reputation: 3381
How about IS NULL?
Edit: Updated query to account for bot h columns being null... getting long and ugly, but working...
SELECT A.* FROM TABLE_A A
INNER JOIN TABLE_B B
ON B.KEY = A.KEY
AND ((B.COL1<>A.COL1 OR (A.COL1 IS NULL AND B.col1 IS NOT NULL) OR (B.COL1 IS NULL AND A.col1 IS NOT NULL ))
OR (B.COL2<>A.COL2 OR (A.COL2 IS NULL AND B.col2 IS NOT NULL) OR (B.COL2 IS NULL AND A.col2 IS NOT NULL )))
Upvotes: 2
Reputation: 453877
You can use this approach
SELECT A.*
FROM TABLE_A A
INNER JOIN TABLE_B B
ON B.KEY = A.KEY
WHERE NOT EXISTS (SELECT A.*
INTERSECT
SELECT B.* )
Upvotes: 9