[翻译][.NET Tip of The Day]Two approaches to update database row if exists, insert if not(zz)

来自: http://dotnettipoftheday.org/tips/update-insert-row-sql.aspx

 

Two approaches to update database row if exists, insert if not

The biggest challenge with update/insert (so called upsert) is to minimize any kind of locks. Unfortunately there is no silver bullet for this yet. So let's review two the most commonly used methods:

1. Update, if @@ROWCOUNT = 0 then insert

    UPDATE Table1 SET Column1 = @newValue WHERE Id = @id

    IF @@ROWCOUNT = 0

    BEGIN

       INSERT INTO Table1 (Id, Column1) VALUES (@id, @newValue)

    END

This method is good if you know that in most of the cases a row will exist and update will be performed. Otherwise the second method should be used.

2. If row exists update, otherwise insert

    IF EXISTS(SELECT * FROM Table1 WHERE Id = @id)

    BEGIN

       UPDATE Table1 SET Column1 = @newValue WHERE Id = @id

    END

    ELSE

    BEGIN

       INSERT INTO Table1 (Id, Column1) VALUES (@id, @newValue)

    END

This one is good if you know that in most of the cases a row will not exist and insert will be performed. For such cases it executes SELECT statement followed by INSERT statement. That results in less expensive lock comparing to UPDATE + INSERT in previous method.

P.S. both methods should be used in transaction with isolation level Serializable.