MSSQL Update/Insert Based Upon Existing Record

Posted: February 4, 2010 in SQL

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.

Advertisements
Comments
  1. […] I posted in my blog entry MSSQL Update/Insert Based Upon Existing Record, it is better to use “IF @@ROWCOUNT=0″ rather than “IF EXISTS ()” when […]

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