This is a pretty common situation that comes up when performing
database operations. A stored procedure is called and the data needs
to be updated if it already exists and inserted if it does not. If we
refer to the Books Online documentation, it gives examples that are
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue') UPDATE Table1 SET (...) WHERE Column1='SomeValue'ELSE INSERT INTO Table1 VALUES (...)
approach does work, however it might not always be the best approach.
This will do a table/index scan for both the SELECT statement and the
UPDATE statement. In most standard approaches, the following statement
will likely provide better performance. It will only perform one
table/index scan instead of the two that are performed in the previous
UPDATE Table1 SET (...) WHERE Column1='SomeValue'IF @@ROWCOUNT=0 INSERT INTO Table1 VALUES (...)
The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.
remember, the examples in the MSDN documentation are usually the
easiest way to implement something, not necessarily the best way. Also
(as I re-learned recently), with any database operation, it is
good to performance test the different approaches that you take.
Sometimes the method that you think would be the worst might actually
outperform the way that you think would be the better way.