T-SQL Is it possible to do an Update / Insert with a single fast operation

Question

Let's say I have a table and I want to insert a row. The new row's key may already match an existing row's 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 the new row should be inserted.

What is the most efficient way to perform such an operation? I was thinking of first doing a SELECT (perhaps with EXISTS) to see if a particular key is present, followed by an UPDATE if present and an INSERT if not. You would probably need to keep an UPDLOCK and a HOLDLOCK for this combination of statements in order to avoid race conditions, as well. This seems overly 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 which does exactly that.

See the MSDN Books Online docs on MERGE for details.

Basically, you need four things:

• a source (table or view or inline SELECT statement)
• a target
• a JOIN condition that links the two
• statements for cases when there's a MATCH (rows exists in both source and target), NOT MATCHED (when row doesn't exist in the target yet) and so forth

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 one statement and highly optimized by SQL Server.