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.
There are no comments for this entry.
[Add Comment]