MS SQL: Atomic Insert or Update (UPSERT)

It is not straight forward to create an atomic insert or update statement in SQL Server, i.e. we update the row if it exists. Otherwise we insert a new row.

The following statement (notice the locks) will create a more or less atomic statement:


-- Start transaction
begin tran


-- Row Exists?
if not exists (select * from <table> with (updlock, rowlock, holdlock) where <PK = ...>    
begin
        <insert>
end
else
begin
        <update>
end

-- End Transaction
commit