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

Try using the below code:

begintran

updateMytable with (serializable) set ...

where key1 = @key1

if @@row_count = 0

begin

insert into Mytable (key1, ...) values (@key1,..)

end

committran

Otherwise, use this:

begintran

if exists (select * from Mytable with (updlock,serializable) where key1 = @key1)

begin

updateMytable set ...

where key1 = @key1

end

else

begin

insert into table (key1, ...)

values (@key1, ...)

end

committran