Starting with SQL Server 2008, you can use a MERGE statement to modify data in a target table based on data in a source table. The statement joins the target to the source by using a column common to both tables, such as a primary key. You can then insert, modify, or delete data from the target table-all in one statement-according to how the rows match up as a result of the join.

The MERGE statement supports several clauses that facilitate the different types of data modifications. In this article, I explain each of these clauses and provide examples that demonstrate how they work. I created the examples on a local instance of SQL Server 2008. To try them out, you’ll need to first run the following script to create and populate the tables used in the examples:

As you can see, the script creates and populates the BookInventory and BookOrder tables. The BookInventory table represents the books that are or were available at a fictional book retailer. If the Quantity value for a book is 0, then the book has sold out.

The BookOrder table shows those books for which an order has been placed and delivered. If the Quantity value for a book listed in this table is 0, then the book had been requested but not included with the delivery. The Quantity values of both tables when added together represent the company’s current inventory.

Implementing the WHEN MATCHED Clause

The first MERGE clause we’ll look at is WHEN MATCHED. You should use this clause when you want to update or delete rows in the target table that match rows in the source table. Rows are considered matching when the joined column values are the same.

For example, if the BookID value in the BookInventory table matches the BookID value in the BookOrder table, the rows are considered to match, regardless of the other values in the matching rows. When rows do match, you can use the WHEN MATCHED clause to modify data in the target table. Lets look at an example to demonstrate how this works.

In the following MERGE statement, I join the BookInventory table (the target) to the BookOrder table (the source) and then use a WHEN MATCHED clause to update the Quantity column in the target table:

As you can see, the statement begins with the MERGE keyword, followed by the name of the target table. Note that the table name should be qualified as necessary. Notice that I’ve also assigned an alias (bi) to the target table to make it easier to reference that table later in the statement.

The next line in the statement is the USING clause, which is made up of the USING keyword, followed by the source table (again, qualified as necessary). I’ve also assigned an alias (bo) to this table. I then used an ON clause to join the two tables, based on the TitleID value in each table (bi.TitleID = bo.TitleID).

After I specified the target and source tables as well as the join condition, I added a WHEN MATCHED clause. The clause includes the WHEN MATCHED keywords, followed by the THEN keyword, next the UPDATE keyword, and finally a SET subclause. In this case, the SET expression specifies that the new Quantity value in the target table should equal the sum of the Quantity values from both the target and source tables (bi.Quantity = bi.Quantity + bo.Quantity). This way, the new Quantity value in the target table will reflect the accurate number of books available for sale, that is, the number of books that were on hand plus the number that arrived with the recent order.

That’s all there is to creating a basic MATCH statement. I also included a SELECT statement so we can view the contents of the target table. The following results reflect the new values in the Quantity column, as they appear in the BookInventory table after it has been updated with the MERGE statement:

TitleID	Title	Quantity
1	The Catcher in the Rye	9
2	Pride and Prejudice	3
3	The Great Gatsby	0
5	Jane Eyre	5
6	Catch 22	0
8	Slaughterhouse Five	4

As the query results indicate, several of the rows have been updated to reflect the total inventory, based on the amounts in both the target and source tables. For example, the row in the BookInventory table with the TitleID value of 1 (The Catcher in the Rye) originally showed three books in stock. However, according to the BookOrder table, six more books were ordered, giving the company a total of nine books. As you would expect, the Quantity value in the BookInventory table is now 9.

You might have noticed that the book with the BookID value of 3 (The Great Gatsby) originally had a Quantity value of 0 in both the source and target tables. Suppose you want to remove from the BookInventory table any book whose Quantity value is 0 in both the target and source tables. You can easily delete such rows by adding a second WHEN MATCHED clause to your MATCH statement, as shown in the following example:

Notice that the new WHEN MATCHED clause includes a specific search condition after the AND keyword (bi.Quantity + bo.Quantity = 0). As a result, whenever rows from the two tables match and the two Quantity columns from the matched rows add up to 0, the row will be deleted, indicated by the DELETE keyword. Now the SELECT statement returns the following results:

TitleID	Title	Quantity
1	The Catcher in the Rye	9
2	Pride and Prejudice	3
5	Jane Eyre	5
6	Catch 22	0
8	Slaughterhouse Five	4

As you can see, the book The Great Gatsby has been removed from the inventory. You should note, however, that a MERGE statement can include at most only two WHEN MATCHED clauses. Whenever you do include two of these clauses, the first clause must include the AND keyword, followed by a search condition, as I’ve done here. The second WHEN MATCHED clause is then applied only if the first one is not.

Implementing the WHEN NOT MATCHED [BY TARGET] Clause

The next clause in the MERGE statement we’ll review is WHEN NOT MATCHED [BY TARGET]. (The BY TARGET keywords are optional.) You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target. For example, the BookOrder table contains a row for Gone with the Wind. However, the BookInventory table does not contain this book. The following example demonstrates how to include a WHEN NOT MATCHED clause in your MERGE statement that adds Gone with the Wind to your target table:

As the statement shows, I first specify the WHEN NOT MATCHED BY TARGET keywords, followed by the THEN keyword, and finally followed by the INSERT clause. The INSERT clause has two parts: the INSERT subclause and the VALUES subclause. You specify the target columns in the INSERT subclause and the source values in the VALUES clause. Notice that, for the VALUES subclause, I must qualify the column names with the table alias. The SELECT statement now returns the following results:

TitleID	Title	Quantity
1	The Catcher in the Rye	9
2	Pride and Prejudice	3
4	Gone with the Wind	4
5	Jane Eyre	5
6	Catch 22	0
7	Age of Innocence	8
8	Slaughterhouse Five	4

Two new rows have been added to the BookInventory table: one for Gone with the Wind and one for Age of Innocence. Because the books existed in the source table, but not in the target table, they were inserted into the BookInventory table.

Implementing the WHEN NOT MATCHED BY SOURCE Clause

As you’ll recall from the discussion about the WHEN MATCHED clause, you can use that clause to delete rows from the target table. However, you can delete a row that matches a row in the source table. But suppose you want to delete a row from the target table that does not match a row in the source table.

For example, one of the rows originally inserted into the BookInventory table is for the book Catch 22. The Quantity value for that book was never updated because no order was placed for the book, that is, the book was never added to the BookOrder table. Because there are no copies of that book in stock, you might decide to delete that book from the target table. To delete a row that does not match a row in the source table, you must use the WHEN NOT MATCHED BY SOURCE clause.

The following example includes a WHEN NOT MATCHED BY SOURCE clause that specifies that any rows with a quantity of 0 that do not match the source should be deleted:

After I specified the WHEN NOT MATCHED BY SOURCE keywords, I specified AND followed by a search condition (bi.Quantity = 0). I then added the THEN keyword, and next the DELETE keyword. The results returned by the SELECT statement are shown in the following table:

TitleID	Title	Quantity
1	The Catcher in the Rye	9
2	Pride and Prejudice	3
4	Gone with the Wind	4
5	Jane Eyre	5
7	Age of Innocence	8
8	Slaughterhouse Five	4

As you can see, the BookInventory table no longer includes the row for Catch 22. And because the three MERGE clauses have been used together, the BookInventory now reflects the exact number of books that are currently on hand, and no books are included that are not in stock.

Implementing the OUTPUT Clause

When SQL Server 2005 was released, it included support for the OUTPUT clause in several data modification language (DML) statements. The OUTPUT clause is also available in the MERGE statement. The OUTPUT clause returns a copy of the data that you’ve inserted into or deleted from your tables. When used with a MERGE statement, the clause provides you with a powerful tool for capturing the modified data for archiving, messaging, or application purposes.

In the following example, I use an OUTPUT clause to pass the outputted data to a variable named @MergeOutput:

Notice that I first declare the @MergeOutput table variable. In the variable, I include a column for the action type plus three additional sets of column. Each set corresponds to the columns in the target table and includes a column that shows the deleted data and one that shows the inserted data. For example, the DelTitleID and InsTitleID columns correspond to the deleted and inserted values, respectively, in the target table.

The OUTPUT clause itself first specifies the built-in $action variable, which returns one of three nvarchar(10) values-INSERT, UPDATE, or DELETE. The variable is available only to the MERGE statement. I follow the variable with a set of column prefixes (DELETED and INSERTED) for each column in the target table. The column prefixes are followed by the name of the column they’re related to. For example, I include DELETED.TitleID and INSERTED.TitleID for the TitleID column in the target table. After I specify the column prefixes, I then include an INTO subclause, which specifies that the outputted values should be saved to the @MergeOutput variable.

After the OUTPUT clause, which is the last clause in my MERGE statement, I added a SELECT statement to retrieve the updated contents of the BookInventory table, as I’ve done in previous examples. The SELECT statement returns the following results:

TitleID	Title	Quantity
1	The Catcher in the Rye	9
2	Pride and Prejudice	3
4	Gone with the Wind	4
5	Jane Eyre	5
7	Age of Innocence	8
8	Slaughterhouse Five	4

Notice that I also include a second SELECT statement in my example above. This statement retrieves the contents of the @MergeOutput variable. The query results are shown in the following table:

ActionType	DelTitleID	InsTitleID	DelTitle	InsTitle	DelQuantity	InsQuantity
UPDATE	1	1	The Catcher in the Rye	The Catcher in the Rye	6	9
DELETE	3	NULL	The Great Gatsby	NULL	0	NULL
INSERT	NULL	4	NULL	Gone with the Wind	NULL	4
UPDATE	5	5	Jane Eyre	Jane Eyre	0	5
DELETE	6	NULL	Catch 22	NULL	0	NULL
INSERT	NULL	7	NULL	Age of Innocence	NULL	8

The results show any actions that were taken on the target table. For instance, the row for the Great Gatsby indicates that this row was deleted from the BookInventory table. The DelTitleID, DelTitle, and DelQuantity columns show the values that were deleted. However, the InsTitleID, InsTitle, and InsQuantity columns all show null values. That’s because no data was inserted into the target table for that row. If a row had been inserted, the InsTitleID, InsTitle, and InsQuantity columns will show the inserted values, but the DelTitleID, DelTitle, and DelQuantity columns will show null values because nothing is deleted when an insert is performed. Any updated rows will have values in all columns.

As you can see, when you use the OUTPUT clause in conjunction with the MERGE statement’s other three clauses-WHEN MATCHED, WHEN NOT MATCHED [BY TARGET], and WHEN NOT MATCHED BY SOURCE-you can perform multiple DML operations and verify your data modifications easily and efficiently. A MERGE statement can simplify your code, improve performance, and reduce your development effort. For more information on the MERGE statement, see the topic “Inserting, Updating, and Deleting Data by Using MERGE” in SQL Server 2008 Books Online.