WHERE IN with composite key.

_{Monday, March 21, 2011 3:28 AM}

I am trying to run an INSERT statement using a SELECT, problem is it is violating primary key constraint. (Cannot inset duplicate key).

Usually I would run a WHERE NOT IN clause, but the table I am checking against has a composite primary key (two seperate fields acting together as primary key).

Is it possible to use WHERE NOT IN with two columns? Or is there a different approach I can use to check whether my dataset contains values that already exist in the table?

_{Monday, March 21, 2011 3:35 AM ✅Answered | 1 vote}

Yes, you want a WHERE NOT EXISTS clause, something like

INSERT MyTable (PK1, PK2, MyData)
SELECT Data1, Data2, Data3
FROM OtherTable ot
WHERE NOT EXISTS (SELECT *
 FROM MyTable m2 WHERE ot.Data1 = m2.PK1 AND ot.Data2 = m2.PK2) 

Tom

_{Monday, March 21, 2011 3:36 AM ✅Answered}

Try WHERE NOT EXISTS...

WHERE
   NOT EXISTS
      (SELECT 1
         FROM TableA
         INNER JOIN TableB
            ON TableA.Column1 = TableB.Column1
               AND TableA.Column2 = TableB.Column2)

-- Martin POON -- Principal Consultant @ AppsGoGo.com -- SQL Server MVP, MCTS, MCITP ~~ http://msMVPs.com/blogs/MartinPOON ~~ http://www.MartinPOON.com

_{Monday, March 21, 2011 3:37 AM | 1 vote}

use a LEFT JOIN to the table that you would normally have in the WHERE IN section and check where a Primary key column IS NULL.

This should perform better as well.

Craig Bryden - Please mark correct answers