Pages Menu
Categories Menu

Posted by on Jul 21, 2011 in SQL | 0 comments

Upsert Over Exists In Stored Procedures

It is a common requirement in database transactions to have a procedure that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. Thus far, I have been doing the (if exists then) method as follows…

IF EXISTS(
 SELECT 1
 FROM MY_TABLE
 WHERE col1=@col1 AND col2=@col2)
    --Update Statement
    UPDATE myTable
    SET Col1=@col1, Col2=@col2
    WHERE ID=@ID
ELSE
    --Insert Statement
    INSERT INTO myTable (Col1, Col2)
    VALUES (@col1, @col2)

I have discovered that there is another (more efficient) method of performing this procedure known as upsert or merge which has a cheaper I/O requirement.

--Update Statement
UPDATE myTable set Col1=@col1, Col2=@col2
WHERE ID=@ID

if @@rowcount = 0
    --Insert Statement
    INSERT INTO myTable (Col1, Col2)
    VALUES (@col1, @col2)

A good discussion on the merits of performing the upsert in this way can be found at SqlServerCentral.com.

For every update in the case mentioned above we are removing one additional read from the table if we use the UPSERT instead of EXISTS. Unfortunately for an Insert, both the UPSERT and IF EXISTS methods use the same number of reads on the table. Therefore the check for existence should only be done when there is a very valid reason to justify the additional I/O. The optimized way to do things is to make sure that you have little reads as possible on the DB.