What is the correct pattern for doing an atomic "UPSERT" (UPDATE where exists,
INSERT otherwise) in SQL Server 2005?
I see a lot of code on SO (e.g. see http://stackoverflow.com/questions/639854/tsql-check-if-a-row-exists-otherwise-insert) with the following two-part pattern:
UPDATE ...
FROM ...
WHERE <condition>
-- race condition risk here
IF @@ROWCOUNT = 0
INSERT ...
or
IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0
-- race condition risk here
INSERT ...
ELSE
UPDATE ...
where will be an evaluation of natural keys. None of the above approaches seem to deal well with concurrency. If I cannot have two rows with the same natural key, it seems like all of the above risk inserting rows with the same natural keys in race condition scenarios.
I have been using the following approach but I'm surprised not to see it anywhere in people's responses so I'm wondering what is wrong with it:
INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
-- race condition risk here?
( SELECT 1 FROM <table> WHERE <natural keys> )
UPDATE ...
WHERE <natural keys>
(Note: I'm assuming that rows will not be deleted from this table. Although it would be nice to discuss how to handle the case where they can be deleted -- are transactions the only option? Which level of isolation?)
Is this atomic? I can't locate where this would be documented in SQL Server documentation.
