How can I do an insert where not exists?

froadie picture froadie · Jan 16, 2011 · Viewed 47k times · Source

I'd like to combine an insert query with a "where not exists" so as not to violate PK constraints. However, syntax such as the following gives me an Incorrect syntax near the keyword 'WHERE' error -

INSERT INTO myTable(columns...)
VALUES(values...)
WHERE NOT EXISTS
   (SELECT *
    FROM myTable
    WHERE pk_part1 = value1,
        AND pk_part2 = value2)

How can I accomplish this?

(In general, can you combine an insert with a where clause?)

Answer

bernd_k picture bernd_k · Jan 16, 2011
INSERT INTO myTable(columns...)
Select values...
WHERE NOT EXISTS
   (SELECT *
    FROM myTable
    WHERE pk_part1 = value1,
        AND pk_part2 = value2)

Edit: After reading martins link, If admit, that the best solution is:

BEGIN TRY
    INSERT INTO myTable(columns...)
    values( values...)
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH;