What is an “UPSERT” ?

A pretty frequent pattern I see in queries and stored procedures is where someone does something like this:

— Check for existence of row, then UPDATE it
IF EXISTS(SELECT * FROM dbo.Customers WHERE CustID = @CustID)
    BEGIN
        UPDATE dbo.Customers
        SET FirstName = @FirstName
        WHERE CustID = @CustID
    END
ELSE
— Otherwise INSERT new row
     INSERT INTO dbo.Customers(FirstName)
     VALUES (@FirstName)

The problem with this (from a performance perspective) is the extra SELECT. If you are reasonably sure that the row will exist, it will be less costly to use this pattern:

— Try the UPDATE first
UPDATE dbo.Customers
SET FirstName = @FirstName
WHERE CustID = @CustID

— Do the INSERT if no rows were updated
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.Customers(FirstName)
    VALUES (@FirstName)
END

The Upsert pattern avoids the initial SELECT. However, if the row does not exist, and you end up having to do the INSERT, then the Upsert pattern will not be any better than the original query. You should try it both ways with your workload.

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s