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)