CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-insert
VSO
VSO

Reputation: 12656

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

Upvotes: 5

Views: 28829

Answers (6)

rajkanani
rajkanani

Reputation: 384

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');

Upvotes: 0

rodmucha
rodmucha

Reputation: 46

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

Upvotes: 2

Mohamed F
Mohamed F

Reputation: 869

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

Upvotes: 0

Randall
Randall

Reputation: 1521

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?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

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;

Upvotes: 0

TZHX
TZHX

Reputation: 5398

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

Upvotes: 19

Related Questions