MERGE statement was introduced in SQL SERVER 2008. It allows you to modify data in a target table based on data in a source table by joining the two tables together using a column common to both the tables. Using MERGE statement, we can perform DML operations such as INSERT, UPDATE and DELETE on a target table in one single statement.

MERGE statements are primarily used in a data warehouse environment where the databases are designed for data analyzing purposes. It is especially useful when new data comes in and decisions need to be made whether the new data needs to be inserted, or whether the existing data needs to be updated or deleted based on the new data. Before MERGE statement was introduced, this used to be achieved using conditional statements scanning tables multiple times followed by separate INSERT, UPDATE and DELETE statements. With MERGE statement, all this can be done with just one statement using one table scan thus improving the performance.

Following are the details for the above syntax:

1. MERGE INTO [target table]
   Here the MERGE keyword is followed by an optional INTO keyword which is then followed by the name of the target table. The target table is the table where rows returned by the WHEN clause will be inserted, updated or deleted.
2. USING [source table]
   Here the USING keyword is followed by the name of the source table. Source table is the table which is used to match rows with the target table. Data will not be updated in the source table.
3. ON [condition]
   Condition defines columns which are used to match the target and the source table. This is similar to the ON clause in the JOIN operator.
4. WHEN MATCHED THEN [action]
   WHEN MATCHED clause defines the actions which are performed on rows in the target table which matches the rows in the source table based on the ON [condition] clause. There can be up to two WHEN MATCHED clause in a MERGE statement. When two WHEN MATCHED clauses are used, one must perform UPDATE action and one must perform DELETE action, also the first WHEN MATCHED clause must include AND operator.
5. WHEN NOT MATCHED BY TARGET THEN [action]
   WHEN NOT MATCHED BY TARGET clause defines the action which needs to be performed on the target table when rows present in the source table does not exist in the target table. This clause is used to insert missing rows into the target table. Unlike WHEN MATCHED clause, there can only be one WHEN NOT MATCHED BY TARGET clause in a single MERGE statement.
6. WHEN NOT MATCHED BY SOURCE THEN [action]
   This clause defines the action which needs to be performed on the target table when a row present in the target table does not exist in the source table. Unlike WHEN NOT MATCHED BY TARGET clause, there can be two WHEN NOT MATCHED BY SOURCE clause in a single merge statement. Just like WHEN MATCHED clause, when two WHEN NOT MATCHED BY SOURCE clauses are defined, one must perform UPDATE action and one must perform DELETE action.

Table: Employee

Table: EmployeeArchive

Following are the changes which needs to be done.

1. Position column for two employees (EmployeeID 3 and 4) has been updated in the Employee table. These need to be updated in the EmployeeArchive table.
2. Employee table has two new employees (EmployeeID 7 and 8). These employees needs to be inserted into the EmployeeArchive table.
3. EmployeeArchive table has two employees (EmployeeID 5 and 6) who have been removed from the Employee table. This means that the Active column in the EmplyeeArchive table needs to be updated to 0.

Let’s break down all the requirements into smaller tasks. First let’s find employees who are common to both the tables and make updates to EmployeeArchive table with data from the Employee table. This will take care of the first requirement by updating the Position column in the EmployeeArchive table for EmployeeID 3 and 4.

In the above query, the MERGE keyword is followed by an optional INTO keyword, name of the target table and an alias for the target table. In this case EmployeeArchive is the target table, and all the operations will be performed on this table.

After defining the target table, the next line defines the source table. In this case, Employee is the source table which will be matched with the target table. The condition of match is defined in the next line after the ON keyword. In this case, the condition is that EmployeeID in the target table should match the EmployeeID in the source table.

After defining both the target and the source tables, the query defines the WHEN MATCHED clause. If you look at the original table, there are four rows (EmployeeID 1, 2, 3, 4) in the target table which matches with the source table. The operations performed within the WHEN MATCHED clause will only affect these four rows. In the query, WHEN MATCHED clause includes an UPDATE action which updates all the columns in the EmployeeArchive table.

Table: EmployeeArchive

From the output, you can see that the position column for EmployeeID 3 and 4 has been updated to the most recent position. You will also notice that LastUpdated column for all the matched rows has been updated including rows for EmployeeID 1 and 2 which did not require any updates. To fix this, we should add condition in the WHEN MATCHED clause so that only the rows which require updates need to be updated.

The above query adds condition in the WHEN MATCHED clause so that UPDATE operation is performed for only those matched rows where the column values in the target table differs from the column values in the source table.

Table: EmployeeArchive

From the output, you can see that LastUpdated date has been changed for only those rows where the data is different from the Employee table. In this case, only rows with EmployeeID 3 and 4 were updated. Rows with EmployeeID 1 and 2 were not updated because none of the values in the EmployeeArchive table differs from values in the Employee table.

It is possible in a MERGE statement to have two WHEN MATCHED clauses. When two WHEN MATCHED clauses are used, one of them must perform an UPDATE action and the other one must perform a DELETE action, also the first clause must include an AND operator.

The following query is just for explanation purposes since this is not part of the requirement.

The above query uses two WHEN MATCHED clauses. The first one checks whether the value in the HireDate column in the EmployeeArchive table is less than 01/01/2009. If it is, then the row is deleted from the EmployeeArchive table. The second WHEN MATCHED clause does an update on EmployeeArchive table if there exists any difference in column values between EmployeeArchive table and Employee table.

Table: EmployeeArchive

To achieve the second requirement, we need to insert new employees (EmployeeID 7 and 8) from Employee table to EmployeeArchive table. For this, we would need to make use of WHEN NOT MATCHED BY TARGET clause. This clause checks for rows which are present in the source table but are missing in the target table.

The above query makes use of the WHEN NOT MATCHED BY TARGET clause to extract the rows which are present in the Employee table but not in EmployeeArchive table. In the query, WHEN NOT MATCHED BY TARGET keywords are followed by an INSERT statement which inserts the extracted rows from the Employee table into EmployeeArchive table.

Table: EmployeeArchive

As you can see in the output, new rows from source table has been inserted into the target table. In this case, EmployeeID 7 and 8 were inserted into EmployeeArchive table.

The final requirement is that, Active column for rows in the EmployeeArchive table which does not exist in the Employee table needs to be updated to 0. That means Active column for rows with EmployeeID 5 and 6 in the EmployeeArchive table needs to be updated to 0. To do this, we need to make use of WHEN NOT MATCHED BY SOURCE clause. This clause checks for rows which are present in the target table but not in the source table.

In the above query, WHEN NOT MATCHED BY SOURCE clause extracts rows from the target table which are not present in the source table. In this case, rows with EmployeeID 5 and 6 in the EmployeeArchive table. Notice that WHEN NOT MATCHED BY SOURCE keywords are followed by an UPDATE statement which updates the extracted rows and sets the Active column to False and LastUpdate column to the current date.

Table: EmployeeArchive

Just like WHEN MATCHED clause, it is possible to have two WHEN NOT MATCHED BY SOURCE clause in a single MERGE statement. WHEN two WHEN NOT MATCHED BY SOURCE clauses are used, then one must perform an UPDATE action and the other one must perform a DELETE action.

The following query is just for explanation purposes since this is not part of the requirement.

The above query gives an example of two WHEN NOT MATCHED BY SOURCE clause in a single MERGE statement. Notice that out of the two clauses, the first one has a condition and performs a DELETE operation for rows in the EmployeeArchive table. The second clause performs an UPDATE operation for rows which were not part of the DELETE operation.

Table: EmployeeArchive

As you can see from the output, row with EmployeeID 5 has been deleted from the EmployeeArchive table. This is because the HireDate column for the row had a value less 01/01/2009. Also notice that for the row with EmployeeID 6, Active column has been updated to 0 and LastUpdated column has been updated to the current date.

Finally let’s put all the clauses together and build the final MERGE statement which will satisfy all the three requirements.

As you can see, the query includes all three WHEN MATCHED, WHEN NOT MATCHED BY TARGET and WHEN NOT MATCHED BY SOURCE clauses. The first clause which is a WHEN MATCHED clause, checks for any changes in column values for rows with matching EmployeeID. WHEN MATCHED clause then updates rows in the EmployeeArchive table where the column values do not match with the values in the Employee table. The second clause which is a WHEN NOT MATCHED BY TARGET clause, checks for rows which exists in the source table but are missing in the target table. WHEN NOT MATCHED BY TARGET clause then inserts the missing records into the EmployeeArchive table. Finally WHEN NOT MATCHED BY SOURCE clause checks for rows in the target table which does not exist in the source table. WHEN NOT MATCHED BY SOURCE clause then updates the target table for rows which does not exist in the source table.

Table: EmployeeArchive

As seen in the output, the query updated Position column for EmployeeID 3 and 4 using the WHEN MATCHED clause. The query then inserted rows for EmployeeID 7 and 8 using WHEN NOT MATCHED BY TARGET clause. Finally the query updated Active column for rows with EmployeeID 5 and 6 using WHEN NOT MATCHED BY SOURCE clause.