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?

Possible duplicate of [Check if a row exists, otherwise insert](https://stackoverflow.com/questions/639854/check-if-a-row-exists-otherwise-insert) — Complex, Oct 16 '17 at 21:19
`BEGIN INSERT INTO [dbo].[Contact_Categories](Contact_Category_ID, Description) SELECT 1, 'Internal' FROM [dbo].[Contact_Categories] WHERE Contact_Category_ID <> 1) END` — Paul Varghese, Oct 16 '17 at 21:20
@PaulVarghese What it it doesn't exist yet? Then there is no `Contact_Category_ID = 1`; — VSO, Oct 16 '17 at 21:22

score 17 · Accepted Answer · answered Oct 16 '17 at 21:22

17

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

answered Oct 16 '17 at 21:22

TZHX

4,946
15
45
55

That's quite elegant, better than the IF construct alternative – MikeAinOz Oct 16 '17 at 21:50

score 1 · Answer 2 · answered Oct 16 '17 at 21:26

The correct way to handle this is by using a unique index/constraint:

create unique index unq_Contact_Categories_Category_Id on Contact_Categories(Contact_Category_ID);

The database will then guarantee the uniqueness for the column. This prevents race conditions.

You can catch this using try/catch:

BEGIN TRY
   INSERT INTO [dbo].[Contact_Categories](Contact_Category_ID, Description)
       SELECT 1, 'Internal';
END TRY
BEGIN CATCH
   PRINT 'Ooops';  -- you can even raise an error if you like.
END CATCH;

score 0 · Answer 3 · answered Oct 16 '17 at 21:31

Why not an If statement?

IF NOT EXISTS 
(select * from [dbo].[Contact_Categories] WHERE Contact_Category_ID = 1)
  begin
    insert into [dbo].[Contact_Categories] (Contact_Category_ID, Description)
    values (1, 'Internal')
  end

This has the advantage of not doing anything if the value exists. Similar to answer provided here: SQL Server IF NOT EXISTS Usage?

score 0 · Answer 4 · answered Oct 16 '17 at 22:08

0

I would do:

INSERT INTO [dbo].[Contact_Categories](Contact_Category_ID, Description)
   VALUES (1, 'Internal')
   WHERE 1 NOT IN ( SELECT Contact_Category_ID FROM [dbo].[Contact_Categories])

answered Oct 16 '17 at 22:08

Mohamed F

734
1
7
13

score 0 · Answer 5 · answered Oct 16 '17 at 22:28

0

Try to replace your query with:

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

answered Oct 16 '17 at 22:28

rodmucha

26
2

This query must be inside a transaction to prevent race conditions, otherwise someone might insert a row between the time that the table was checked and the insertion of the row. – rodmucha Oct 16 '17 at 22:41

score 0 · Answer 6 · answered Sep 17 '21 at 09:59

I also had the same problem, this is my solution.

insert into Contact_Categories (Contact_Category_ID, Description)
   select 1, 'Internal' 
where not exists 
   (select * from Contact_Categories where Contact_Category_ID = 1 and Description = 'Internal');

SQL - Insert Where Not Exists

6 Answers6

Linked