0

I have a temp table which has a subset of rows copied from another table and updated. While inserting them back into the original table I want to capture their inserted id with the original id in the temp table.

Unfortunately I am getting an error

The multipart identifier "temp.id" could not be bound.

DECLARE @MappingTable TABLE (
    old_id BIGINT,
    new_id BIGINT
)

SELECT *
INTO #Table1Temp
WHERE col1 = true;

INSERT INTO dbo.table1 ([col1], [col2], [col3], [col4])
    OUTPUT
        inserted.id, temp.id
        INTO @MappingTable (new_id, old_id)
SELECT [col1], [col2], [col3], [col4]
FROM #Table1Temp temp;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Chris Parmenter
  • 1
  • 1
  • 1
    You can **only** `OUTPUT` columns that get **inserted** - not arbitrary columns from the query. So unless you somehow insert that `temp.id` into `dbo.table1`, you won't be able to include it in the `OUTPUT` list of columns – marc_s Jul 01 '21 at 05:28
  • 1
    The way around it is to use a [merge instead](https://dba.stackexchange.com/questions/191146/using-source-columns-in-output-into-clause-of-an-insert-statement-sql-server) – Dale K Jul 01 '21 at 05:28
  • @marc_s Please vote for the dupe – Charlieface Jul 01 '21 at 10:42

0 Answers0