Friday, October 7, 2011

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
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

0 kommentarer:

Post a Comment