Using MERGE to override OUTPUT clause limitations

The OUTPUT clause is a very powerful extension of T-SQL included in SQL Server 2005. But from time to time it needs some tweaks or workarounds to get the expected result. One such example is using OUTPUT with INSERT statements. There is a limitation that only columns from the table to which we are inserting can be used in OUTPUT. I will show you how to override this limitation with a small trick using the MERGE statement added to SQL Server in version 2008.

Let’s prepare two tables we will play with. One of them will be [dbo].[TargetTable] where data will be inserted from the second table [dbo].[SourceTable].

DROP TABLE IF EXISTS [dbo].[TargetTable]
DROP TABLE IF EXISTS [dbo].[SourceTable]
GO

CREATE TABLE [dbo].[TargetTable] (
	[ID] INT NOT NULL,
	[Value] NVARCHAR(100)
)
GO

CREATE TABLE [dbo].[SourceTable] (
	[ID] INT NOT NULL,
	[Value] NVARCHAR(100),
	[OtherValue] NVARCHAR(100)
)
GO

INSERT INTO [dbo].[SourceTable]
	( [ID], [Value], [OtherValue] )
	VALUES 
	(1, 'Val1', 'OthVal1' ),
	(2, 'Val2', 'OthVal2' ),
	(3, 'Val3', 'OthVal3' )
GO

Once everything is prepared we can try the following statement to insert data from the source table to the target table. We would like also to output some columns we will use for review or processing later (if we will use table or table variable as OUTPUT target):

INSERT INTO [dbo].[TargetTable]
	( [ID],	[Value] )
		OUTPUT [Inserted].[ID], [Inserted].[Value], [src].[OtherValue]
	SELECT 
		[src].[ID], [src].[Value]
	FROM [dbo].[SourceTable] [src]
GO

The statement has failed because of the column [src].[OtherValue] isn’t one of the columns inserted into the target table and therefore it isn’t allowed to be referenced in the OUTPUT clause.

But what should be done if we really need this column in the output? We can simply get it when MERGE will be used instead of INSERT:

MERGE INTO [dbo].[TargetTable] [trg]
USING ( SELECT [ID], [Value], [OtherValue]
		FROM [dbo].[SourceTable]
	  ) AS [src] ON 1 = 0
WHEN NOT MATCHED THEN
	INSERT ( [ID],[Value] )
	VALUES ([src].[ID], [src].[Value])
		OUTPUT [Inserted].[ID], [Inserted].[Value], [src].[OtherValue];
GO

MERGE is much flexible to be used together with the OUTPUT clause and is suitable for a lot of special scenarios like this one. INSERT still have its place. Using MERGE everywhere instead of INSERTED isn’t a good idea. People reading our code will be confused and MERGE has still a lot of issues and hidden drawbacks unresolved.