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

10

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

answered Nov 23, 2019 at 14:37

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related