t1 has an automatically generated primary key called pkId
INSERT INTO t1( title, summary)
OUTPUT inserted.pkId, t2.id INTO @IdTable(New_Id, Old_Id)
SELECT t2.title, t2.summary
FROM t2
Can someone please tell me why this doesn't work?
I'm getting the error The multi-part identifier "t2.id" could not be bound.
You can't get the values from t2 in the OUTPUT clause on INSERT (you can for UPDATE and DELETE).
from_table_name
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.
Note that INSERT isn't mentioned
You'd have to
From Comments
It doesn't work because you are not selecting t2.id in your SELECT statement. To make it work, you should add t2.id to your statement but that would also require you to INSERT this ID in t1 which (I assume) is not what you need. I'm still pondering on how to resolve that elegantly.
Not an elegant solution but the easiest might be to
t2ID column to t1INSERT to include the t2ID (not using an OUTPUT clause)@IdTableALTER TABLE t1 ADD COLUMN t2ID INTEGER NULL
GO
INSERT INTO t1( title, summary, t2ID)
SELECT t2.title, t2.summary, t2.ID
FROM t2
INSERT INTO @IdTable
SELECT pkID, t2ID
FROM t1
WHERE t2ID IS NOT NULL
GO
ALTER TABLE t1 DROP COLUMN t2ID
GO
t2.idin yourSELECTstatement. To make it work, you should addt2.idto your statement but that would also require you toINSERTthis ID int1wich (I assume) is not what you need. I'm still pondering on how to resolve that elegantly.