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

don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
When multiple threads will try to perform Insert-or-update you can easily get primary key violation.

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

To avoid deadlocks and PK violations you can use something like this:

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 

Answer 2

See my detailed answer to a very similar previous question

@Beau Crawford's is a good way in SQL 2005 and below, though if you're granting rep it should go to the first guy to SO it. The only problem is that for inserts it's still two IO operations.

MS Sql2008 introduces merge from the SQL:2003 standard:

merge tablename with(HOLDLOCK) as target using (values ('new value', 'different value'))     as source (field1, field2)     on target.idfield = 7 when matched then     update     set field1 = source.field1,         field2 = source.field2,         ... when not matched then     insert ( idfield, field1, field2, ... )     values ( 7,  source.field1, source.field2, ... ) 

Now it's really just one IO operation, but awful code :-(