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

Published by Waqas at 9:00 AM under Tips n Tricks | SQL Server

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.