Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009

    Question 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
    Union All
    Select ImageID From Table3
    Union All
    Select ImageID From Table4)

    If this will work, how do I write out the resulting list of "differences" (ImageIDs) into a separate Table5 ? THANKS !!!!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts