SQL Server : output inserted value as well as value from SELECT

Question

I would like to insert into a TempTable the inserted column value, which I have working, as well as a column from the original select all in one shot. Is this possible?

This works where a is the PK that is auto-generated and b and c are other columns.

INSERT INTO App.Table1 (b, c) OUTPUT inserted.a INTO #TempTable SELECT b, c FROM App.Table2

Can I do something like:

INSERT INTO App.Table1 (b, c) OUTPUT t2.a, inserted.a INTO #TempTable SELECT b, c, a FROM App.Table2 t2

Effectively I want a #TempTable that has the original PK of the row and the new PK so I have a lookup table essentially.

"Is this possible?" no. The OUTPUT clause can only return values from the inserted and deleted pseudo tables. — Thom A
– Thom A ♦, Commented Nov 23, 2019 at 14:30

GMB · Accepted Answer · 2019-11-23 14:37:07Z

As commented by Larnu, OUTPUT can not refer to columns coming from the SELECT part of the INSERT.

You could work around this with a MERGE statement:

MERGE INTO App.Table1 USING App.Table2 AS t2 ON 1 = 0 WHEN NOT MATCHED THEN INSERT((b, c)) Values(t2.b, t2.c) OUTPUT inserted.a, t2.a INTO #TempTable

Thom A · Accepted Answer · 2019-11-23 14:37:12Z

"Is this possible?" No. The OUTPUT clause can only return details of rows effected in the DML statement (those inserted in this case).

This can easily be tested with the below batch:

USE Sandbox; GO CREATE TABLE dbo.MyTable1 (ID int IDENTITY, SomeString varchar(10)); CREATE TABLE dbo.MyTable2 (ID int IDENTITY, SomeString varchar(10)); INSERT INTO dbo.MyTable1 (SomeString) VALUES('abc'),('def'); GO INSERT INTO dbo.MyTable2 (SomeString) OUTPUT MT1.ID, inserted.ID SELECT MT1.ID FROM dbo.MyTable1 MT1 ORDER BY MT1.ID DESC; GO DROP TABLE dbo.MyTable1; DROP TABLE dbo.MyTable2;

This will result in the below error:

Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "MT1.ID" could not be bound.

This is also implied from the documentation:

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

Emphasis added. A row from the source table (dbo.MyTable1.ID is this case) isn't affected by the INSERT, UPDATE, DELETE, or MERGE statement, so is inaccessible from the OUTPUT clause.

Edit: GMB has added a nice work around, however, using MERGE, as the rows are affected by the MERGE statement and thus accessible from the OUTPUT clause.

Collectives™ on Stack Overflow

SQL Server : output inserted value as well as value from SELECT

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related