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

    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
    Location
    In front of the computer
    Posts
    15,579
    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.

    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.

Posting Permissions

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