tsql - T SQL merge example needed to help comprehension

Question

The following:

MERGE dbo.commissions_history AS target
USING (SELECT @amount, @requestID) AS source (amount, request)
ON (target.request = source.request)
WHEN MATCHED THEN
    UPDATE SET amount = source.amount
WHEN NOT MATCHED THEN
    INSERT (request, amount)
    VALUES (source.request, source.amount);

from https://stackoverflow.com/a/2967983/857994 is a pretty nifty way to do insert/update (and delete with some added work). I'm finding it hard to follow though even after some googling.

Can someone please:

• explain this a little in simple terms - the MSDN documentation mutilated my brain in this case.
• show me how it could be modified so the user can type in values for amount & request instead of having them selected from another database location?

Basically, I'd like to use this to insert/update from a C# app with information taken from XML files I'm getting. So, I need to understand how I can formulate a query manually to get my parsed data into the database with this mechanism.

Answer 1

If you aren't familiar with join statements then that is where you need to start. Understanding how joins work is key to the rest. Once you're familiar with joins then understanding the merge is easiest by thinking of it as a full join with instructions on what to do for rows that do or do not match.

So, using the code sample provided lets look at the table commissions_history

|  Amount  |   Request  |   <other fields> |
--------------------------------------------
|  12.00   |   1234     |   <other data>   |
|  14.00   |   1235     |   <other data>   |
|  15.00   |   1236     |   <other data>   |

The merge statement creates a full join between a table, called the "target" and an expression that returns a table (or a result set that is logically very similar to a table like a CTE) called the "source".

In the example given it is using variables as the source which we'll assume have been set by the user or passed as a parameter.

DECLARE @Amount Decimal = 18.00;
DECLARE @Request Int = 1234;

MERGE dbo.commissions_history AS target       
USING (SELECT @amount, @requestID) AS source (amount, request)       
ON (target.request = source.request)   

Creates the following result set when thought of as a join.

|  Amount  |   Request  |   <other fields> | Source.Amount | Source.Request  |
------------------------------------------------------------------------------
|  12.00   |   1234     |   <other data>   |   18.00       |     1234        |
|  14.00   |   1235     |   <other data>   |   null        |     null        |
|  15.00   |   1236     |   <other data>   |   null        |     null        |

Using the instructions given on what to do to the target on the condition that a match was found.

WHEN MATCHED THEN        
UPDATE SET amount = source.amount    

The resulting target table now looks like this. The row with request 1234 is updated to be 18.

|  Amount  |   Request  |   <other fields> |
--------------------------------------------
|  18.00   |   1234     |   <other data>   |
|  14.00   |   1235     |   <other data>   |
|  15.00   |   1236     |   <other data>   |

Since a match WAS found nothing else happens. But lets say that the values from the source were like this.

DECLARE @Amount Decimal = 18.00;
DECLARE @Request Int = 1239;

The resulting join would look like this:

|  Amount  |   Request  |   <other fields> | Source.Amount | Source.Request  |
------------------------------------------------------------------------------
|  12.00   |   1234     |   <other data>   |   null        |     null        |
|  14.00   |   1235     |   <other data>   |   null        |     null        |
|  15.00   |   1236     |   <other data>   |   null        |     null        |
|  null    |   null     |   null           |   18.00       |     1239        |

Since a matching row was not found in the target the statement executes the other clause.

WHEN NOT MATCHED THEN                                 
INSERT (request, amount)                                 
VALUES (source.request, source.amount);  

Resulting in a target table that now looks like this:

|  Amount  |   Request  |   <other fields> |
--------------------------------------------
|  12.00   |   1234     |   <other data>   |
|  14.00   |   1235     |   <other data>   |
|  15.00   |   1236     |   <other data>   |
|  18.00   |   1239     |   <other data>   |

The merge statements true potential is when the source and target are both large tables. As it can do a large amount of updates and/or inserts for each row with a single simple statement.

A final note. It's important to keep in mind that not matched defaults to the full clause not matched by target, however you can specify not matched by source in place of, or in addition to, the default clause. The merge statement supports both types of mismatch (records in source not in target, or records in target not in source as defined by the on clause). You can find full documentation, restrictions, and complete syntax on MSDN.