Solutions for INSERT OR UPDATE on SQL Server
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 tranif exists (select * from table with (updlock,serializable) where key = @key)begin update table set ... where key = @keyendelsebegin insert into table (key, ...) values (@key, ...)endcommit tran
or
begin tran update table with (serializable) set ... where key = @key if @@rowcount = 0 begin insert into table (key, ...) values (@key,..) endcommit 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 targetusing (values ('new value', 'different value')) as source (field1, field2) on target.idfield = 7when 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 :-(
Do an UPSERT:
UPDATE MyTable SET FieldA=@FieldA WHERE Key=@KeyIF @@ROWCOUNT = 0 INSERT INTO MyTable (FieldA) VALUES (@FieldA)