Chris 6:13 pm on February 4, 2010  

MSSQL Update/Insert Based Upon Existing Record

In the past I learned how to combine one query to correctly update or insert a record based upon if it exists or not. I would only use this if I didn’t already know if it existed because an un-needed select statement would be run and it increases the size of the query string. Example:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)

Thanks to Jeremiah Clark’s Blog, I’ve found an often better way of doing this.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)

The first example will do a table/index scan for both the SELECT and UPDATE statements. The second example will perform one less table/index scan, likely increasing performance.