Overview

• OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.
• These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.
• The results can also be inserted into a table or table variable.
• Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
• You can omit INTO clause if you don't want to store the results.

OUTPUT with INSERT

DECLARE @MyTableVar TABLE
(
	NewScrapReasonID SMALLINT,
	[Name] VARCHAR(50),
	ModifiedDate DATETIME
);

INSERT INTO Production.ScrapReason
OUTPUT
	[INSERTED].ScrapReasonID,
	[INSERTED].[Name],
	[INSERTED].ModifiedDate
	INTO @MyTableVar
VALUES ('Operator error', GETDATE());

OUTPUT with DELETE

DELETE Production.ScrapReason
OUTPUT [DELETED].* 
INTO @MyTableVar
WHERE [Name] = 'Operator error';

OUTPUT with UPDATE

UPDATE Production.ScrapReason
SET ModifiedDate = GETDATE() 
OUTPUT
	[DELETED].ScrapReasonID,
	[DELETED].[Name],
	[DELETED].ModifiedDate,
	[INSERTED].ScrapReasonID,
	[INSERTED].[Name],
	[INSERTED].ModifiedDate
INTO @MyTableVar
WHERE [Name] = 'Operator error';

OUTPUT with MERGE

DECLARE @OutputTable TABLE
(
	ActionTaken nvarchar(10),
	ExistingCode nchar(3),
	ExistingName nvarchar(50),
	ExistingDate datetime,	
	NewCode nchar(3),
	NewName nvarchar(50),
	NewDate datetime
);

DECLARE @UnitMeasureCode NCHAR(3) = 'ABC',
    @Name NVARCHAR(25) = 'Test Value';

MERGE Production.UnitMeasure AS [Tar]
USING (SELECT @UnitMeasureCode, @Name) AS [Src] ([UnitMeasureCode], [Name])
ON ([Tar].UnitMeasureCode = [Src].UnitMeasureCode)
WHEN MATCHED THEN
	UPDATE 
		SET [Name] = [Src].[Name]
WHEN NOT MATCHED THEN
	INSERT 
	(
		[UnitMeasureCode],
		[Name]
	)
	VALUES ([Src].UnitMeasureCode, [Src].[Name])
OUTPUT 
	$action, 
	[DELETED].*, 
	[INSERTED].* 
INTO @OutputTable;

SELECT * FROM @OutputTable

OUTPUT Clause Limitations

• Some of the scenarios where the OUTPUT clause is not supported:

1. DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
2. INSERT statements that contain an EXECUTE statement.
3. Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
4. The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.