I recently read this article on upsert functionality in SQL server 2008. I thought that the SQL server team finally had come to theri senses and added functionality similar to replace in MySQL, something that have been available in MySQL for ages. But no. The article describes a way to use the new merge command in order to perform an "upsert".

 

An upsert is an operation where rows in the database are updated if they exists and inserted if they don't. This has been available as a replace statement in MySQL for quite some time. At first glance the merge seems much more cumbersome to use than the replace, but don't be fooled by this. One problem I've had with the replace command is knowing exactly what rows will be updated and which will be added. Especially when updating things that are part of a multi-column primary key. The merge however is really more clear in what it actually does and you have full control of what you update and what you insert.