|
CHAPTER 8
Data Modification
273
The MERGE statement defines an UPDATE action when a match is found, setting the target
companyname, phone, and address values to those of the corresponding row from the source.
Notice that the syntax of the UPDATE action is similar to a normal UPDATE statement, except that
you don’t need to provide the name of the table that is the target of the update because it was
already defined in the MERGE clause.
The MERGE statement defines an INSERT action when a match is not found, inserting the row from
the source to the target. Again, the syntax of the INSERT action is similar to a normal INSERT state-
ment, except that you don’t need to provide the name of the table that is the target of the activity
because it was already defined in the MERGE clause.
The MERGE statement reports that five rows were modified.
(5 row(s) affected)
This includes three rows that were updated (customers 2, 3, and 5) and two that were inserted
(customers 6 and 7). Query the Customers table to get the new contents.
SELECT * FROM dbo.Customers;
This query returns the following output.
custid companyname phone address
----------- ------------------- -------------------- ----------
1 cust 1 (111) 111-1111 address 1
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
4 cust 4 (444) 444-4444 address 4
5 BBBBB CCCCC DDDDD
6 cust 6 (new) (666) 666-6666 address 6
7 cust 7 (new) (777) 777-7777 address 7
The WHEN MATCHED clause defines what action to take when a source row is matched by a target
row. The WHEN NOT MATCHED clause defines what action to take when a source row is not matched
by a target row. T-SQL also supports a third clause that defines what action to take when a target row
is not matched by a source row; this clause is called WHEN NOT MATCHED BY SOURCE. For example,
suppose that you want to add logic to the MERGE example to delete rows from the target when the
target row is not matched by a source row. All you need to do is add the WHEN NOT MATCHED BY
SOURCE clause with a DELETE action, like this.
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
www.it-ebooks.info
274
Microsoft SQL Server 2012 T-SQL Fundamentals
Query the Customers table to see the result of this MERGE statement.
SELECT * FROM dbo.Customers;
This query returns the following output, showing that customers 1 and 4 were deleted.
custid companyname phone address
----------- ------------------- -------------------- ----------
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB CCCCC DDDDD
6 cust 6 (new) (666) 666-6666 address 6
7 cust 7 (new) (777) 777-7777 address 7
Going back to the first MERGE example, which updates existing customers and adds nonexistent
ones, you can see that it is not written in the most efficient way. The statement doesn’t check whether
column values have actually changed before overwriting the attributes of an existing customer. This
means that a customer row is modified even when the source and target rows are identical. You can
address this by adding predicates to the different action clauses by using the AND option; except
for the original condition, action will take place only if the additional predicate evaluates to TRUE. In
this case, you need to add a predicate under the WHEN MATCHED AND clause that checks whether
at least one of the attributes changed to justify the UPDATE action. The complete MERGE statement
looks like this.
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED AND
( TGT.companyname <> SRC.companyname
OR TGT.phone <> SRC.phone
OR TGT.address <> SRC.address) THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);
As you can see, the MERGE statement is very powerful, allowing you to express modification logic
with less code and more efficiently than the alternatives.
Do'stlaringiz bilan baham: | 
