January 27, 2010 at 2:32 pm
I have production table A with a composite key consisting of 9 fields and i have a stage table B same as table A. How do i check if all records of stage table B are avaialbe in table A.
January 27, 2010 at 2:39 pm
Do an left join using all key fields. Any field with a NULL is missing.
January 27, 2010 at 2:56 pm
how do i do left join on multiple columns ?
January 27, 2010 at 3:18 pm
SELECT
*
FROM
TableA AS A
LEFT OUTER JOIN
TableB AS B
ON A.Col1 = B.Col1
AND A.Col2 = B.Col2
...
AND A.Col9 = B.Col9
WHERE
B.Col1 IS NULLIf the tables are on different servers you will need to fully qualify the table on the other server.
January 27, 2010 at 3:19 pm
You could also use EXCEPT. This query returns any rows in B that aren't also in A:
SELECT * FROM B
EXCEPT
SELECT * FROM A;
January 27, 2010 at 3:22 pm
David, yours is a MUCH better answer. Thanks. I had forgotten about the EXCEPT.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply