Search code examples
sqlsql-serverclause

sql output clause error

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.

Solution

  • 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

    • Add a dummy t2ID column to t1
    • Alter your INSERT to include the t2ID (not using an OUTPUT clause)
    • Select all new values into your @IdTable
    • Remove the dummy column

    SQL Script

    ALTER 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