How can I do an insert where not exists?

Question

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?)

Solution 1

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;

Solution 2

The simplest way to keep a unique list of values is to either a) set the column(s) as the primary key or b) create a unique constraint on the column(s). Either of these would result in an error when attempting to insert/update values to something that already exists in the table, when a NOT EXISTS/etc would fail silently -- no error, query will execute properly.

That said, use an INSERT/SELECT (don't include the VALUES portion):

INSERT INTO myTable(columns...)
SELECT [statically defined values...]
  FROM ANY_TABLE
 WHERE NOT EXISTS (SELECT NULL
                     FROM myTable
                    WHERE pk_part1 = value1
                      AND pk_part2 = value2)

Comments

[froadie]

It generates a PK error... I want to avoid that error by checking to make sure it's there first

[OMG Ponies]

@froadie: I understand, but I'd be using the error returned to be able to inform the user that the value(s) already exist.

[Martin Smith]

This can still lead to attempts to insert duplicates under load. If the OP is on SQL Server 2008 using Merge avoids this issue.

[Martin Smith]

As described here stackoverflow.com/questions/3407857/…

[bernd_k]

@Martin I guess, I have to rewrite more, than this answer

[Jonathan Leffler]

EAFP or JFDI

[bernd_k]

The same question for Oracle seems to be answered in stackoverflow.com/questions/3147874/…

[Yosra Nagati]

In mysql, It does not work without adding (from tableName ) after first select , INSERT INTO myTable(columns...) Select values... from myTable WHERE NOT EXISTS

[Mostafa Barmshory]

If the tuple exist then the following error raised: VALUES clause must contain at least one element. Empty elements are not allowed.