Sql server® 2012 t-sql fundamentals

MERGE

 with 

OUTPUT

You can also use the OUTPUT clause with the MERGE statement, but remember that a single MERGE 

statement can invoke multiple different DML actions based on conditional logic. This means that 

a single MERGE statement might return through the OUTPUT clause rows that were produced by 

different DML actions. To identify which DML action produced the output row, you can invoke a func-

tion called $action in the OUTPUT clause, which will return a string representing the action (INSERT, 

UPDATE, or DELETE). To demonstrate the use of the OUTPUT clause with the MERGE statement, I’ll 

use one of the examples from the “Merging Data” section earlier in this chapter. To run this example, 

make sure you rerun Listing 8-2 to re-create the dbo.Customers and dbo.CustomersStage tables.

The following code merges the contents of CustomersStage into Customers, updating the attri-

butes of customers who already exist in the target and adding customers who don’t.

MERGE INTO 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) 

OUTPUT $action AS theaction, inserted.custid, 

  deleted.companyname AS oldcompanyname, 

  inserted.companyname AS newcompanyname, 

  deleted.phone AS oldphone, 

  inserted.phone AS newphone, 

  deleted.address AS oldaddress, 

  inserted.address AS newaddress;

www.it-ebooks.info

---