LearnNowOnline

Blog

Advanced Query Techniques

Excerpt by Don Kiely | October 01, 2013

The T-SQL INSERT, UPDATE, and DELETE statements provide a lot of power and flexibility for maintaining data in a relational database. But they can also be unwieldy. Say that you have a table with rows that you need to use to update another table. Depending on the data in the source table, you might have to update or delete an existing row or, if no related row exists, insert it into the table.

This can be unwieldy, requiring several statements to perform the various actions. And it can be a pain to get all the comparisons working just right. The MERGE statement addresses this issue. It lets you insert, update, and delete table data with just a single statement, based on the similarities and differences in the data.

It joins a data source table with a target table or view to perform the various actions. The operation it performs depends on the results of the join:

• If data exists in both the source and the target, update the changed data in the target.
• If data in the source doesn't exist in the target, insert data into the target.
• If data exists in the target but not in the source, delete the data from the target.

You have full control over how you define whether and how each of these actions will occur for a particular row in the source table. The MERGE statement is transactional, so you don't need to explicitly define a transaction. If any of the operations fails, the entire statement rolls back.

• The MERGE statement uses five clauses:
• The MERGE clause specifies the target data, such as a table, view, or other query.
• The USING clause defines the source data.
• The ON clause specifies how to join the tables, much like a regular JOIN clause.
• One or more WHEN clauses define the actions to take based on the join matching.
• The OUTPUT clause returns a row for each action taken.

The WHEN clause is where all the actions occur, and you're likely to have more than one in a typical MERGE statement. The WHEN clause has three forms:

•  WHEN MATCHED [AND condition]

Here is where you either update or delete an existing row in the target data. You can have at most two of these clauses, and if you have two, one must have a condition associated with it. You can define either an UPDATE or DELETE statement in this clause. If you're using an UPDATE statement and more than one row in the target matches the statement, you get an error, and you can't update any row in the target more than once. Similarly, you can't update and delete the same row.

•  WHEN NOT MATCHED [BY TARGET] [AND condition]

This is where you can use an INSERT statement, when a row in the source doesn't match any row in the target. You can have only one of this WHEN form in any MERGE statement. The BY TARGET clause is optional, because it is the default.

•  WHEN NOT MATCHED BY SOURCE [AND condition]

This form of the WHEN clause kicks in when rows in the target don't match a row in the source; in this case you can either update or delete the row in the target. You can have at most two of these in a MERGE statement, and the second must have a condition associated with it. 

The optional condition on some of the versions of the WHEN clause can be  just about anything you can include in a WHERE clause. You have to include  at least one WHEN clause in the MERGE statement, although the order you include the different forms doesn't matter at all. What does matter is that if you include the same form with and without a condition, the clause with the condition must be first. Any triggers that exist on the affected target table will fire normally, but there is no guarantee of the order in which triggers fire if multiple operations are performed on the target table. So be careful of side effects. All normal permissions apply, so the user or security context must be able to perform the various actions.

This post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely.