Search code examples
sqlsql-servert-sqlsql-server-2012sql-insert

SQL - Insert Where Not Exists

I have what I thought to be a completely trivial query - insert values into a table if a value with a matching ID does not exist:

BEGIN
   INSERT INTO [dbo].[Contact_Categories](Contact_Category_ID, Description)
   VALUES (1, 'Internal')
   WHERE NOT EXISTS( SELECT * FROM [dbo].[Contact_Categories] WHERE Contact_Category_ID = 1)
END

I get an error around the where statement. Why? How do I accomplish my goal?

enter image description here

Solution

  • Your problem comes from WHERE being valid for UPDATE/SELECT but INSERT just doesn’t understand what it means.

    But you can get around this. Change your code to be like:

    BEGIN
   INSERT INTO [dbo].[Contact_Categories](Contact_Category_ID, Description)
   SELECT 1, 'Internal'
   WHERE NOT EXISTS( SELECT * FROM [dbo].[Contact_Categories] WHERE Contact_Category_ID = 1)
END