t-sql NOT IN with multiple columns

Question

Hello,

I have a Microsoft SQL database, where i am trying to insert some data. I have a unique key on 4 columns and i want to insert data from multiple tables into this table while checking the data to make sure it will not violate the uniqueness of the key. If i was doing this on a single column, i would do a NOT IN, like so..

INSERT TABLE_A (FLD_1)
    SELECT FLD_1
        FROM TBL_B
        INNER JOIN TBL_C
            ON TBL_B.FLD_1 = TBL_C.FLD_1
    WHERE TBL_B.FLD_1 NOT IN
        (
        SELECT TBL_A.FLD_1 FROM TBL_A
        )

Any thoughts?

Answer 1

Use NOT EXISTS instead since you have to deal with multiple columns.

http://www.techonthenet.com/sql/exists.php

EDIT:

Untested, but roughly it will be this:

SELECT FLD_1
FROM TBL_B
INNER JOIN TBL_C  ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS 
    (
    SELECT TBL_A.FLD_1 FROM TBL_A INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1
    )

For a multi-column check it would be roughly this:

SELECT FLD_1, FLD_2, FLD_3, FLD_4)
FROM TBL_B
INNER JOIN TBL_C  ON TBL_B.FLD_1 = TBL_C.FLD_1
WHERE NOT EXISTS 
    (
    SELECT TBL_A.FLD_1, TBL_A.FLD_2, TBL_A.FLD_3, TBL_A.FLD_4 
    FROM TBL_A 
    INNER JOIN TBL_B ON TBL_B.FLD1 = TBL_A.FLD1 AND 
                        TBL_B.FLD2 = TBL_A.FLD2 AND 
                        TBL_B.FLD3 = TBL_A.FLD3 AND 
                        TBL_B.FLD4 = TBL_A.FLD4 
    )

Answer 2

It would probably be easier to insert the row, and check afterwards to see if the insert was successful. The unique constraint will be checked fairly quickly as it is an index.