Can I use "if not exists" to insert a row if the row is not already present in sql server?

Question

insert into Attributes (Id, Disabled, AttributeValue) 
values (@id, @disabled, @attr_value)
if not exists
(
select * from Attributes
where 
Id = @id
)

Not sure, if this is a valid query.
I have seen people use where not exists. What is the difference and how to use where not exists? When I put where not exists, it is saying "Incorrect syntax near where."

I checked these question as well. But, it doesn't seem to have query to insert using if not exists. Only inserting a row if it's not already there and sql conditional insert if row doesn't already exist

Am I missing something? Should I only use where not exists?

score 11 · Answer 1 · answered Mar 28 '12 at 16:24

11

Change it to INSERT INTO SELECT

INSERT INTO Attributes (Id, Disabled, AttributeValue) 
SELECT @id, @disabled, @attr_value
WHERE NOT EXISTS
(
    select * from Attributes
    where 
    Id = @id
)

answered Mar 28 '12 at 16:24

Taryn

242,637
56
362
405

score 3 · Accepted Answer · answered Mar 28 '12 at 16:22

3

You would need to execute something like this

IF NOT EXISTS (select 1 from Attributes where Id = @id)
BEGIN
    insert into Attributes (Id, Disabled, AttributeValue) 
    values (@id, @disabled, @attr_value)
END

This is typically done with an else clause to update the row if it exists.

answered Mar 28 '12 at 16:22

Ash Burlaczenko

24,778
15
68
99

I think you could have a race condition with this - where an Attribute with the given ID is inserted after the test by something else, but you still insert. Bluefeet's answer is better because it's executed as a single query and so avoids the possibility of a race condition. – Peter Mar 04 '14 at 15:19

score 2 · Answer 3 · answered Mar 29 '12 at 11:40

Consider using MERGE:

MERGE INTO Attributes 
   USING ( values (@id, @disabled, @attr_value ) ) 
            AS source ( Id, Disabled, AttributeValue )
      ON source.Id = Attributes.Id
WHEN NOT MATCHED THEN
   INSERT ( Id, Disabled, AttributeValue )
      VALUES ( Id, Disabled, AttributeValue );

One advantage is that you can also update the values when the Id does exist e.g.

MERGE INTO Attributes 
   USING ( values (@id, @disabled, @attr_value ) ) 
            AS source ( Id, Disabled, AttributeValue )
      ON source.Id = Attributes.Id
WHEN MATCHED THEN
   UPDATE
      SET Id = source.Id,
          Disabled = source.Disabled, 
          AttributeValue = source.AttributeValue
WHEN NOT MATCHED THEN
   INSERT ( Id, Disabled, AttributeValue )
      VALUES ( Id, Disabled, AttributeValue );

What if I don't want to update, but just insert? Would MERGE perform better than the two queries mentioned above? or you think it would be easier to modify if one day I chose to update apart from insert? — , Mar 29 '12 at 13:44
Even for `INSERT` actions alone I think there is a good chance `MERGE` will perform better. I suggest you test it using typical data. Speaking of future proofing, consider that `MERGE` is standard SQL and widely supported (Oracle, PostgreSQL, DB2, etc) and therefoer higly portable when the time comes to move your successful application to a new platform :) — onedaywhen, Mar 29 '12 at 14:14

Can I use "if not exists" to insert a row if the row is not already present in sql server?

3 Answers3

Linked