If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: XCheck column across big SQL Server tables

We are using SQL Server 2003. I have the following technical issue. Table1 contains a column of interest, call it "ImageID". Table1 contains approx. 9 million rows, but I am interested in ONLY the single column. I then have to combine table information from Table2, Table3, & Table4 which all contain the same column called "ImageID". I need to write a query that returns a set of "ImageIDs" that represent the list of "ImageIDs" that are only present in Table1 but do NOT exist in the aggregrate of Table2, Table3, & Table4 -- the aggregate of these 3 tables contain about 5 million rows. Is performance a concern? Will an EXCEPT or LEFT OUTER JOIN be potential answers? And how do I write out the result set to a new Table5 once I land on the correct SQL statement to identify the differences (example, Table1 contains 100,000 ImageIDs that are not in Tables 2, 3, or 4 and I need to write out these 100,00 ImageIDs to Table5)?

I was thinking of doing the following (using a Sub-Select to create the aggregate of Tables 2, 3, 4) to return the "difference" result set:

There isn't a SQL 2003 and the EXCEPT clause only exists in SQL 2008. I'm going to take Lewis Grizzard's advice: "Shoot low boys, they're riding Shetland Ponies" and go for an approach that will work on SQL 2000 and all later versions.

Code:

SELECT Table1.ImageID
FROM Table1
INTO Table5
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.ImageID = Table1.ImageID)
AND NOT EXISTS (SELECT * FROM Table3 WHERE Table3.ImageID = Table1.ImageID)
AND NOT EXISTS (SELECT * FROM Table4 WHERE Table4.ImageID = Table1.ImageID)

-PatP

In theory, theory and practice are identical. In practice, theory and practice are unrelated.