Solutions for INSERT OR UPDATE on SQL Server

Question

Assume a table structure of MyTable(KEY, datafield1, datafield2...).

Often I want to either update an existing record, or insert a new record if it doesn't exist.

Essentially:

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

What's the best performing way to write this?

Answer 1

That depends on the usage pattern. One has to look at the usage big picture without getting lost in the details. For example, if the usage pattern is 99% updates after the record has been created, then the 'UPSERT' is the best solution.

After the first insert (hit), it will be all single statement updates, no ifs or buts. The 'where' condition on the insert is necessary otherwise it will insert duplicates, and you don't want to deal with locking.

UPDATE <tableName> SET <field>=@field WHERE key=@key;

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END

Answer 2

I had tried below solution and it works for me, when concurrent request for insert statement occurs.

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

Answer 3

Do a select, if you get a result, update it, if not, create it.