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 sqlservercentral.com:

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 DB.

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 required.

Edit: 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.

Please read the post on my blog for a good, safe pattern you can use. There are a lot of considerations, and the accepted answer on this question is far from safe.

For a quick answer try the following pattern. It will work fine on SQL 2000 and above. SQL 2005 gives you error handling which opens up other options and SQL 2008 gives you a MERGE command.

begin tran   update t with (serializable)   set hitCount = hitCount + 1   where pk = @id   if @@rowcount = 0   begin      insert t (pk, hitCount)      values (@id,1)   endcommit tran

If to be used with SQL Server 2000/2005 the original code needs to be enclosed in transaction to make sure that data remain consistent in concurrent scenario.

BEGIN TRANSACTION Upsertupdate myTable set Col1=@col1, Col2=@col2 where ID=@IDif @@rowcount = 0insert into myTable (Col1, Col2) values (@col1, @col2)COMMIT TRANSACTION Upsert

This will incur additional performance cost, but will ensure data integrity.

Add, as already suggested, MERGE should be used where available.