Solutions for INSERT OR UPDATE on SQL Server

Question

Consider the MyTable table structure. I frequently want to either insert a new record if one doesn't already exist or edit an existing record.

Essentially:

IF (key exists)
  run update command
ELSE
  run insert command

What would be the most effective way to write this?

Answer 1

Remember to account for transactions. Performance is excellent, but a straightforward (IF EXISTS..) approach is extremely risky.
You can easily have a primary key violation when numerous threads attempt to perform an insert-or-update operation.

You can employ something similar to this to prevent deadlocks and PK violations:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

Or:

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

Hope this helps you.