T-SQL Is it possible to perform an update / insert with one quick operation

Question

Let's say I have a table and I want to insert a row. The new row key may already match the existing row key in the table, in which case I want to update the existing row. Or it may not exist in the table, in which case a new row should be inserted.

What is the most efficient way to perform such an operation? I thought to do SELECT first (possibly with EXISTS ) to see if any particular key is present, and then UPDATE if it is, and INSERT if not. You probably need to keep UPDLOCK and HOLDLOCK for this combination of instructions to avoid race conditions. It seems too complicated and inefficient.

I was wondering if there was a more efficient way to do this in SQL Server 2008R2.

Answer 1

SQL Server 2008 and newer have a MERGE statement that does just that.

For more information, see the Email Documents for MSDN at MERGE .

Basically, you need four things:

• a source (table or view or built-in SELECT statement)
• a target
• a JOIN condition that binds two
• for cases where there is MATCH (strings exist both in the source and in the target), NOT MATCHED (when the string does not yet exist in the target), etc.

So, you basically define something like:

 MERGE (targettable) AS t USING (sourcetable) AS s ON (JOIN condition between s and t) WHEN MATCHED THEN UPDATE SET t.Col1 = s.Col1, t.Col2 = s.Col2 (etc.) WHEN NOT MATCHED THEN INSERT(Col1, Col2, ..., ColN) VALUES(s.Col1, s.Col2, ......, s.ColN) 

This is done as a single statement and is highly optimized by SQL Server.