<p>I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this:</p>

<pre><code>update myTable set Col1=@col1, Col2=@col2 where ID=@ID
if @@rowcount = 0
insert into myTable (Col1, Col2) values (@col1, @col2)
</code></pre>

<p>My logic behind writing it in this way is that the update will perform an implicit select using the where clause and if that returns 0 then the insert will take place.</p>

<p>The alternative to doing it this way would be to do a select and then based on the number of rows returned either do an update or insert. This I considered inefficient because if you are to do an update it will cause 2 selects (the first explicit select call and the second implicit in the where of the update). If the proc were to do an insert then there'd be no difference in efficiency.</p>

<p>Is my logic sound here?
Is this how you would combine an insert and update into a stored proc?</p>

[{'answer_id': 13554, 'author': 'binOr', 'author_id': 990, 'author_profile': 'https://Stackoverflow.com/users/990', 'pm_score': 7, 'selected': True, 'text': '<p>Your assumption is right, this is the optimal way to do it and it\'s called <a href="http://en.wikipedia.org/wiki/Upsert" rel="noreferrer">upsert/merge</a>.</p>\n\n<p><a href="http://www.sqlservercentral.com/articles/T-SQL/61773/" rel="noreferrer">Importance of UPSERT - from sqlservercentral.com</a>: </p>\n\n<blockquote>\n  <p>For every update in the case mentioned above we are removing one\n  additional read from the table if we\n  use the UPSERT instead of EXISTS.\n  Unfortunately for an Insert, both the\n  UPSERT and IF EXISTS methods use the\n  same number of reads on the table.\n  Therefore the check for existence\n  should only be done when there is a\n  very valid reason to justify the\n  additional I/O. The optimized way to\n  do things is to make sure that you\n  have little reads as possible on the\n  DB.</p>\n  \n  <p>The best strategy is to attempt the\n  update. If no rows are affected by the\n  update then insert. In most\n  circumstances, the row will already\n  exist and only one I/O will be\n  required.</p>\n</blockquote>\n\n<p><strong>Edit</strong>: \nPlease check out <a href="https://stackoverflow.com/questions/13540/insert-update-stored-proc-on-sql-server/193876#193876">this answer</a> and the linked blog post to learn about the problems with this pattern and how to make it work safe.</p>\n'}, {'answer_id': 13555, 'author': 'Jon Galloway', 'author_id': 5, 'author_profile': 'https://Stackoverflow.com/users/5', 'pm_score': 3, 'selected': False, 'text': '<p>MERGE is one of the new features in SQL Server 2008, by the way.</p>\n'}, {'answer_id': 25318, 'author': 'Dima Malenko', 'author_id': 2586, 'author_profile': 'https://Stackoverflow.com/users/2586', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>BEGIN TRANSACTION Upsert\nupdate myTable set Col1=@col1, Col2=@col2 where ID=@ID\nif @@rowcount = 0\ninsert into myTable (Col1, Col2) values (@col1, @col2)\nCOMMIT TRANSACTION Upsert\n</code></pre>\n\n<p>This will incur additional performance cost, but will ensure data integrity.</p>\n\n<p>Add, as already suggested, MERGE should be used where available.</p>\n'}, {'answer_id': 35364, 'author': 'Natron', 'author_id': 3682, 'author_profile': 'https://Stackoverflow.com/users/3682', 'pm_score': 2, 'selected': False, 'text': "<p>Big fan of the UPSERT, really cuts down on the code to manage.  Here is another way I do it:  One of the input parameters is ID, if the ID is NULL or 0, you know it's an INSERT, otherwise it's an update.  Assumes the application knows if there is an ID, so wont work in all situations, but will cut the executes in half if you do.</p>\n"}, {'answer_id': 39529, 'author': 'Kevin Fairchild', 'author_id': 3743, 'author_profile': 'https://Stackoverflow.com/users/3743', 'pm_score': 1, 'selected': False, 'text': '<p>Your logic seems sound, but you might want to consider adding some code to prevent the insert if you had passed in a specific primary key.</p>\n\n<p>Otherwise, if you\'re always doing an insert if the update didn\'t affect any records, what happens when someone deletes the record before you "UPSERT" runs?  Now the record you were trying to update doesn\'t exist, so it\'ll create a record instead.  That probably isn\'t the behavior you were looking for.</p>\n'}, {'answer_id': 40476, 'author': 'Simon Munro', 'author_id': 3893, 'author_profile': 'https://Stackoverflow.com/users/3893', 'pm_score': 3, 'selected': False, 'text': '<p>If you are not doing a merge in SQL 2008 you must change it to:</p>\n\n<p>if @@rowcount = 0 and @@error=0</p>\n\n<p>otherwise if the update fails for some reason then it will try and to an insert afterwards because the rowcount on a failed statement is 0</p>\n'}, {'answer_id': 80790, 'author': 'Tomas Tintera', 'author_id': 15136, 'author_profile': 'https://Stackoverflow.com/users/15136', 'pm_score': 3, 'selected': False, 'text': '<p>You not only need to run it in transaction, it also needs high isolation level. I fact default isolation level is Read Commited and this code need Serializable. </p>\n\n<pre><code>SET transaction isolation level SERIALIZABLE\nBEGIN TRANSACTION Upsert\nUPDATE myTable set Col1=@col1, Col2=@col2 where ID=@ID\nif @@rowcount = 0\n  begin\n    INSERT into myTable (ID, Col1, Col2) values (@ID @col1, @col2)\n  end\nCOMMIT TRANSACTION Upsert\n</code></pre>\n\n<p>Maybe adding also the @@error check and rollback could be good idea.</p>\n'}, {'answer_id': 193876, 'author': 'Sam Saffron', 'author_id': 17174, 'author_profile': 'https://Stackoverflow.com/users/17174', 'pm_score': 6, 'selected': False, 'text': '<p>Please read the <a href="http://samsaffron.com/blog/archive/2007/04/04/14.aspx" rel="noreferrer">post on my blog</a> 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. </p>\n\n<p>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. </p>\n\n<pre><code>begin tran\n   update t with (serializable)\n   set hitCount = hitCount + 1\n   where pk = @id\n   if @@rowcount = 0\n   begin\n      insert t (pk, hitCount)\n      values (@id,1)\n   end\ncommit tran\n</code></pre>\n'}, {'answer_id': 10399131, 'author': 'thughes78013', 'author_id': 1367900, 'author_profile': 'https://Stackoverflow.com/users/1367900', 'pm_score': 2, 'selected': False, 'text': '<p>Modified Dima Malenko post:</p>\n\n<pre><code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE \n\nBEGIN TRANSACTION UPSERT \n\nUPDATE MYTABLE \nSET    COL1 = @col1, \n       COL2 = @col2 \nWHERE  ID = @ID \n\nIF @@rowcount = 0 \n  BEGIN \n      INSERT INTO MYTABLE \n                  (ID, \n                   COL1, \n                   COL2) \n      VALUES      (@ID, \n                   @col1, \n                   @col2) \n  END \n\nIF @@Error &gt; 0 \n  BEGIN \n      INSERT INTO MYERRORTABLE \n                  (ID, \n                   COL1, \n                   COL2) \n      VALUES      (@ID, \n                   @col1, \n                   @col2) \n  END \n\nCOMMIT TRANSACTION UPSERT \n</code></pre>\n\n<p>You can trap the error and send the record to a failed insert table. <br />\nI needed to do this because we are taking whatever data is send via WSDL and if possible fixing it internally. </p>\n'}]
['https://Stackoverflow.com/questions/13540', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1463/']