SQL OUTPUT CLUASE

--OUTPUT CLAUSE  
--1 INSERT WITH OUTPUT  
IF OBJECT_ID('Rows') IS NOT NULL DROP TABLE ROWS  
CREATE TABLE [ROWS](COL INT IDENTITY(1,1),DATACOL VARCHAR(20))  
 
INSERT INTO [ROWS]  
    OUTPUT inserted.COL,inserted.DATACOL  
    SELECT COMPANYNAME FROM Customer  
--RESULTS  
COL         DATACOL  
----------- --------------------  
1           Microsoft  
2           Intel  
3           alibaba  
4           siemens  
5           Bank  
6           GOOGLE  
 
(6 row(s) affected)  
 
-- DELETE WITH OUTPUT  
DELETE FROM [ROWS]  
       OUTPUT deleted.COL,  
              deleted.DATACOL  
              WHERE COL<10 
                
-- UPDATE WITH OUTPUT  
   UPDATE [ROWS]  
   SET DATACOLDATACOL=DATACOL+'--'  
   OUTPUT inserted.COL,  
          deleted.DATACOL AS OLDDATA,  
          inserted.DATACOL AS NEWDATA  
   WHERE COL<11       
--RESULTS  
COL         OLDDATA              NEWDATA  
----------- -------------------- --------------------  
7           Microsoft            Microsoft--  
8           Intel                Intel--  
9           alibaba              alibaba--  
10          siemens              siemens--  
 
(4 row(s) affected)  
     
     
--MERGE WITH OUTPUT  
MERGE INTO dbo.Customer AS TGT  
USING dbo.CustomerStage AS SRC  
ON TGT.CUSID=SRC.CUSID  
WHEN MATCHED THEN  
     UPDATE  
     SET TGT.CustomeName=SRC.CustomeName,  
         TGT.companyname=SRC.companyname  
WHEN NOT MATCHED THEN  
     INSERT (cusid,CustomeName,companyname)  
     VALUES (SRC.cusid,SRC.CustomeName,SRC.companyname)      
OUTPUT $ACTION,  
       INSERTED.CUSID,  
       DELETED.CustomeName AS OLDCustomeName,  
       INSERTED.CustomeName AS NEWCustomeName,   
       DELETED.companyname AS OLDcompanyname,  
       INSERTED.companyname AS companyname;  
         
$ACTION    CUSID       OLDCustomeName       NEWCustomeName       OLDcompanyname       companyname  
---------- ----------- -------------------- -------------------- -------------------- --------------------  
UPDATE     1           guo hu               guo hu               wicresoft            Microsoft  
UPDATE     3           jun wen li           jun wen li           HP                   alibaba  
UPDATE     4           jin hao liu          jin hao liu          IBM                  siemens  
INSERT     5           NULL                 cheng fan            NULL                 Bank  
 
(4 row(s) affected)