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.