OUTPUT Clause

Returns information from, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. Output clause works like a trigger, but if we create a trigger it will write all the data into audit table related to that event(Insert or Update or Delete). By using OUTPUT clause we can write required information into audit table.

Check the below code to know how it works :


CREATE TABLE TEST(ID INT IDENTITY(1,1),NAME VARCHAR(10),GENDER CHAR(1))
CREATE TABLE TEST_AUDIT(ID INT,NAME VARCHAR(10),GENDER CHAR(1),LST_UPDT_DTM DATETIME DEFAULT GETDATE())

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

INSERT INTO TEST
OUTPUT INSERTED.ID, INSERTED.NAME, INSERTED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
SELECT 'SATYA', 'M'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

UPDATE TEST SET NAME = 'KRISHNA'
OUTPUT INSERTED.ID, INSERTED.NAME, INSERTED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
WHERE NAME = 'SATYA'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

DELETE FROM TEST
OUTPUT DELETED.ID, DELETED.NAME, DELETED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
WHERE NAME = 'KRISHNA'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT