<p>MySQL has this incredibly useful yet proprietary <code>REPLACE INTO</code> SQL Command. </p>

<p>Can this easily be emulated in SQL Server 2005?</p>

<p>Starting a new Transaction, doing a <code>Select()</code> and then either <code>UPDATE</code> or <code>INSERT</code> and <code>COMMIT</code> is always a little bit of a pain, especially when doing it in the application and therefore always keeping 2 versions of the statement.</p>

<p>I wonder if there is an easy and <strong>universal</strong> way to implement such a function into SQL Server 2005?</p>

[{'answer_id': 238, 'author': 'Karl Seguin', 'author_id': 34, 'author_profile': 'https://Stackoverflow.com/users/34', 'pm_score': 4, 'selected': False, 'text': '<p>The functionality you\'re looking for is traditionally called an UPSERT. Atleast knowing what it\'s called might help you find what you\'re looking for.</p>\n\n<p>I don\'t think SQL Server 2005 has any great ways of doing this. 2008 introduces the MERGE statement that can be used to accomplish this as shown in: <a href="http://www.databasejournal.com/features/mssql/article.php/3739131" rel="noreferrer">http://www.databasejournal.com/features/mssql/article.php/3739131</a> or <a href="http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx" rel="noreferrer">http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx</a></p>\n\n<p>Merge was available in the beta of 2005, but they removed it out in the final release.</p>\n'}, {'answer_id': 243, 'author': 'Dillie-O', 'author_id': 71, 'author_profile': 'https://Stackoverflow.com/users/71', 'pm_score': 4, 'selected': False, 'text': '<p>What the upsert/merge is doing is something to the effect of...</p>\n\n<pre><code>IF EXISTS (SELECT * FROM [Table] WHERE Id = X)\n   UPDATE [Table] SET...\nELSE\n   INSERT INTO [Table]\n</code></pre>\n\n<p>So hopefully the combination of those articles and this pseudo code can get things moving.</p>\n'}, {'answer_id': 12202, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 7, 'selected': True, 'text': '<p>This is something that annoys me about MSSQL (<a href="http://bizvprog.blogspot.com/2008/04/annoying-fundamental-flaw-with-sql.html" rel="noreferrer">rant on my blog</a>). I wish MSSQL supported <code>upsert</code>. </p>\n\n<p>@Dillie-O\'s code is a good way in older SQL versions (+1 vote), but it still is basically two IO operations (the <code>exists</code> and then the <code>update</code> or <code>insert</code>)</p>\n\n<p>There\'s a slightly better way on <a href="https://stackoverflow.com/questions/13540/insert-update-stored-proc-on-sql-server">this post</a>, basically:</p>\n\n<pre><code>--try an update\nupdate tablename \nset field1 = \'new value\',\n    field2 = \'different value\',\n    ...\nwhere idfield = 7\n\n--insert if failed\nif @@rowcount = 0 and @@error = 0\n    insert into tablename \n           ( idfield, field1, field2, ... )\n    values ( 7, \'value one\', \'another value\', ... )\n</code></pre>\n\n<p>This reduces it to one IO operations if it\'s an update, or two if an insert. </p>\n\n<p>MS Sql2008 introduces <code>merge</code> from the SQL:2003 standard:</p>\n\n<pre><code>merge tablename as target\nusing (values (\'new value\', \'different value\'))\n    as source (field1, field2)\n    on target.idfield = 7\nwhen matched then\n    update\n    set field1 = source.field1,\n        field2 = source.field2,\n        ...\nwhen not matched then\n    insert ( idfield, field1, field2, ... )\n    values ( 7,  source.field1, source.field2, ... )\n</code></pre>\n\n<p>Now it\'s really just one IO operation, but awful code :-(</p>\n'}, {'answer_id': 112313, 'author': 'Sam Saffron', 'author_id': 17174, 'author_profile': 'https://Stackoverflow.com/users/17174', 'pm_score': 3, 'selected': False, 'text': '<p>I wrote a <a href="http://www.samsaffron.com/blog/archive/2007/04/04/14.aspx" rel="nofollow noreferrer">blog post</a> about this issue.</p>\n<p>The bottom line is that if you want cheap updates and want to be safe for concurrent usage, try:</p>\n<pre><code>update t\nset hitCount = hitCount + 1\nwhere pk = @id\n\nif @@rowcount &lt; 1 \nbegin \n   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\n   commit tran\nend\n</code></pre>\n<p>This way you have 1 operation for updates and a max of 3 operations for inserts. So, if you are generally updating, this is a safe cheap option.</p>\n<p>I would also be very careful not to use anything that is unsafe for concurrent usage. It\'s really easy to get primary key violations or duplicate rows in production.</p>\n'}]
['https://Stackoverflow.com/questions/234', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/91/']