12-01-09, 14:10 #1Registered User
- Join Date
- Dec 2009
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:
Select ImageID From Table1 EXCEPT
Select ImageID From
(Select ImageID From Table2
Select ImageID From Table3
Select ImageID From Table4)
If this will work, how do I write out the resulting list of "differences" (ImageIDs) into a separate Table5 ? THANKS !!!!
12-01-09, 14:54 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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.
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)In theory, theory and practice are identical. In practice, theory and practice are unrelated.