Merge statements allow you to perform three different actions depending on the results of a table join.

There are three possible boolean (yes or no) checks that can be used to determine which action you’d like to be taken. They Are:

When Matched Then – This will contain the statement that you’d like to execute when there is a match in both tables. Up to two “When Matched Then” clauses can be specified within a single Merge statement using an additional join statement added with an AND. When two “When Matched Then” statements are specified, you must make sure that one performs an Update, and the other a Delete. The second will only ever be executed when the first is not.

When Not Matched By Source – This section will contain the SQL that will be executed when there is a record held in the Target table, but not in the Source table.

When Not Matched By Target – This is the opposite of the above, and will contain the code executed when a record exists in the Source table, but not the Target table.

An example of the syntax may make the above clearer:

MERGE INTO Person P
USING Employees E ON E.EmployeeId = P.PersonId
WHEN MATCHED THEN
   UPDATE
   SET LastUpdated = GETDATE()
WHEN NOT MATCHED BY SOURCE THEN
   DELETE
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Name, Age, EmployementStatus)
   VALUES (E.Name, E.Age, E.EmployementStatus);

Notice the semicolon (;) at the end of the statement. This is essential for the statement to execute.

The above example will update the LastUpdated column in the Person table if there is a match in both the Person and Employees table.

When there isn’t a match in the Employees table (The Source) for a record that exists in the Person table (The Target), the record that’s in the Person table will be deleted.

When there isn’t a match in the Person table (The Target) for a record that exists in the Employees table (The Source), the record that’s in the Employees table will be inserted.

Each of the DML (Data Manipulation Langauge – Delete, Update, Insert (and Merge, actually)) statements can have the Output and Output Into clauses applied to them within the Merge statement.