SQL - Insert Where Not Exists

Question

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?

Answer 1

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