I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
    insert into table1(id, value1,value2...)
    select (@id, @value1, @value2...)

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?


I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.

set transaction isolation level serializable;
begin transaction;

update dbo.table1
  set value = @value
  where id = @id;

if (@@ROWCOUNT = 0)
  insert dbo.table1(id, value1,value2...)
    values(@id, @value1, @value2...);

commit transaction;

I blogged about this since writing this answer:

  • Appreciate your answers! How about using IF EXISTS (select top 1 1 from table1 where id @id) then update and else Insert?
    – blue
    Mar 20 '19 at 13:11
  • 1
    @blue that pattern requires an extra access to the table (in the case where it exists). IF EXISTS essentially does the exact same thing the update does to try to locate the row - why do that twice? Mar 20 '19 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.