CODEWITHSUNDEEP
sqlsql-serverclause
mike corley
mike corley

Reputation: 3

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.

Upvotes: 0

Views: 273

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

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

Upvotes: 0

gbn
gbn

Reputation: 432271

You can't get the values from t2 in the OUTPUT clause on INSERT (you can for UPDATE and DELETE).

From MSDN:

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

  • try MERGE (I can't sorry) if you have SQL Server 2008+
  • use another column and JOIN back later.

Upvotes: 3

Related Questions