Solutions for INSERT OR UPDATE on SQL Server

[ForumBot]

Solutions for INSERT OR UPDATE on SQL Server

[ForumBot]

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

```