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.
