SQL UPSERT
Posted by mxhxrdba on January 31, 2008
What is an UPSERT?
An UPSERT is a technique to perform inserts/updates in a database using the least amount of database reads. For maximum performance, you want to absolutely minimize the number of reads required of the database.
An UPSERT is really just an UPDATE combined with an INSERT. The classic example, is if you are writing a stored proc to look for a row and update it; if it does not exist, then insert. Most programmers will use an IF, EXISTS syntax to accomplish this. This technique will perform two I/O operations regardless of the existence of the row: one to see if the row exists, and a second to either update or insert. A basic UPSERT syntax is:
———————————–
UPDATE dbo.tObject
SET objectStatusCode = ‘Active’
WHERE objectNumber = 100
IF @@ROWCOUNT = 0
INSERT INTO dbo.tObject
(objectNumber,
objectName)
VALUES
(100,
‘Test Object’)
GO
–You can run the following before and after the statement to view I/O stats:
SET STATISTICS IO ON
–Statement here
SET STATISTICS IO OFF
———————————–
If an INSERT is required, then we will have 2 I/O operations, but if only an UPDATE is required, it would only be 1 I/O operation. The alternative with an IF, EXISTS syntax is to always have 2 I/O’s.
Anonymous said
Upsert is a much harder then problem then this would imply. Consider two simultaneous updates, both fail, because the record does not exist yet. Both attempt to insert, but one fails.
You can use a heavy isolation to solve, but in general that is a very heavyweight solution. I tend to code with the possibility for a failure on the insert and then retry the update.