To see the changes that occur, you can compare the
output of this code with the contents of the Customers
table before the change (which Table 1 shows).
Now, run the full MERGE statement from Listing
1, within the context of a transaction. Table 3 shows
the updated contents of the Customers table. The attributes
of customers 2, 3, and 5 were updated because
those source customers existed in the target. Rows for
source customers 6 and 7 were inserted because they
existed in the source but not in the target.
OUTPUT Clause and $action Function
If you want your MERGE statement to output the
rows that are modified, you can add the OUTPUT
clause at the end of the statement. The semantics of the OUTPUT clause when used with the MERGE
statement are similar to those when used with INSERT,
UPDATE, and DELETE statements. Namely, in the
OUTPUT clause you can refer to deleted. | *> and inserted. | *>. When a row is
deleted by a DELETE action, deleted attributes in the
output clause return attributes from the deleted row,
and inserted attributes return NULLs. When a row is
inserted by an INSERT action, deleted attributes in the
output clause return NULLs, and inserted attributes
return attributes from the inserted row. When a row is
updated by an UPDATE action, deleted attributes in
the output clause return attributes from the old image
of the row (before the change), and inserted attributes
return attributes from the new image of the row (after
the change). You can also refer to the $action function
in the OUTPUT clause to return a string representing
the action that modified the row (i.e., INSERT,
UPDATE, DELETE).
To illustrate using the OUTPUT clause and the
$action function, edit the code in Listing 1 by deleting
the final semicolon and adding the following text:
MERGE …
OUTPUT $action, deleted.*, inserted.*;
Figure 1 shows the output from running this modified
MERGE statement. Note that three rows were updated
(customers 2, 3, and 5). Attributes from the deleted table (i.e., the second through fifth columns in Figure 1)
show the old image of the rows, and attributes from the inserted table (i.e., the sixth through ninth columns in Figure 1) show the new image. Two rows were inserted
(customers 6 and 7). Attributes from the deleted table
show NULLs, and attributes from the inserted table
show the values in those new rows.
Ready, Set, MERGE
I’ve covered the fundamentals of the MERGE statement.
For additional details, including some of
MERGE’s more advanced aspects, as well as its
restrictions, see the Web-exclusive sidebar “Beyond
MERGE’s Basics,” www.sqlmag.com, InstantDoc ID 97965.
The new MERGE statement is undoubtedly
powerful and elegant. Although my examples illustrate using the MERGE statement in an OLTP
environment, you can easily adopt these examples in
a data warehouse environment. You’ll probably find
MERGE to be useful in both types of environment.
In “Grouping Sets, Part 1” (October 2007, InstantDoc
ID 96805) and “Grouping Sets, Part 2” (November
2007, InstantDoc ID 97007), I discussed SQL Server
2008’s new features related to grouping sets, and I provided
example of using the new MERGE statement to
handle incremental updates of aggregates stored in a
data warehouse.
End of Article
Prev. page
1
[2]
next page -->
