Skip to content
Advertisement

Solutions for INSERT OR UPDATE on SQL Server

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:

What’s the best performing way to write this?

Advertisement

Answer

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:

or

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement