MS SQL: Atomic Insert or Update (UPSERT) in Transact-SQL

Microsoft SQL ServerLeave a Comment on MS SQL: Atomic Insert or Update (UPSERT) in Transact-SQL

MS SQL: Atomic Insert or Update (UPSERT) in Transact-SQL

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

In the code above PK is the primary key that will be used to identify one specific row.

Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top