Insert Update stored proc on SQL Server

Your assumption is right, this is the optimal way to do it and it’s called upsert/merge.

Importance of UPSERT – from

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

The best strategy is to attempt the
update. If no rows are affected by the
update then insert. In most
circumstances, the row will already
exist and only one I/O will be

Please check out this answer and the linked blog post to learn about the problems with this pattern and how to make it work safe.

Leave a Comment