How to Compare 2 tables for equality 1

[trpnbillie]

Hi everyone,

I have two tables (with the same structure) that i want to compare for equality. Is there an easier way to do it than 2 left joins? If possible, I would like just one statement to return a single result (same or different). Unfortunately, there are 5 fields that i am comparing

This is what I have:

DECLARE @result varchar(300)
SET @result = 'Same'

--- Look for records in Test1 that are not in Test2
SELECT *
FROM TEST1 X LEFT JOIN TEST2 Y
ON X.ID = Y.ID
AND X.Field2 = Y.Field2
AND X.Field3 = Y.Field3
AND X.Field4 = Y.Field4
AND X.Field5 = Y.Field5
WHERE Y.ID IS NULL

IF @@Rowcount > 1
@Result = 'Different'

--- Look for records in Test2 that are not in Test1
SELECT *
FROM TEST2 Y LEFT JOIN TEST1 X
ON X.ID = Y.ID
AND X.Field2 = Y.Field2
AND X.Field3 = Y.Field3
AND X.Field4 = Y.Field4
AND X.Field5 = Y.Field5
WHERE X.ID IS NULL

IF @@Rowcount > 1
@Result = 'Different'

Surely there is an easier way to do this! I am not sure if mine is even foolproof!

Thanks everyone for any help!

 

[hmckillop]

you can use Union to do it in one go.

SELECT * 
FROM TEST1 X LEFT JOIN TEST2 Y
        ON X.ID = Y.ID
           AND X.Field2 = Y.Field2
           AND X.Field3 = Y.Field3
           AND X.Field4 = Y.Field4
           AND X.Field5 = Y.Field5
WHERE Y.ID IS NULL    
UNION
SELECT * 
FROM TEST2 Y LEFT JOIN TEST1 X
        ON X.ID = Y.ID
           AND X.Field2 = Y.Field2
           AND X.Field3 = Y.Field3
           AND X.Field4 = Y.Field4
           AND X.Field5 = Y.Field5
WHERE X.ID IS NULL

"I'm living so far beyond my income that we may almost be said to be living apart

 

[trpnbillie]

Now don't i feel silly! Thank you so much for your help!!!