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
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 🙁