1

I am trying to use OUTPUT clause to select a column but failed. Here is a quick example,

create table A (id int, name varchar(10))
create table B (id int)
insert into A values(1,'a'),(2,'b'),(3,'c')

declare @Inserted TABLE (ID INT, name varchar(10));

INSERT INTO B
OUTPUT INSERTED.ID, a.name INTO @Inserted
select id from a

I am getting this error,

The multi-part identifier "a.name" could not be bound.
Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322

1 Answers1

3

The OUTPUT clause can only refer to the Inserted (and possibly Deleted) pseudo tables - change your statement to:

INSERT INTO B
OUTPUT INSERTED.ID, INSERTED.name INTO @Inserted
  SELECT id 
  FROM a

Then it should work just fine.

Oskar
  • 1,996
  • 1
  • 22
  • 39
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • got this example from, OUTPUT deleted.ScrapReasonID, http://technet.microsoft.com/en-us/library/ms177564.aspx inserted.ScrapReasonID, inserted.WorkOrderID, inserted.ProductID, p.Name INTO @MyTestVar – Imran Qadir Baksh - Baloch Dec 26 '13 at 08:16
  • See here p.Name is used – Imran Qadir Baksh - Baloch Dec 26 '13 at 08:17
  • 1
    @user960567: true - but that's also an `UPDATE` statement and not an `INSERT`. It seems that with an `UPDATE`, you can refer to tables that are used in the `FROM` clause - while with the `INSERT`, that doesn't seem to work. Don't ask me **why** - just noticed..... – marc_s Dec 26 '13 at 08:26
  • It seems I can use MERGE with OUTPUT here. – Imran Qadir Baksh - Baloch Dec 26 '13 at 08:36
  • `B` doesn't have a column called `name`. So `INSERTED.name` doesn't exist. The workaround is to use `MERGE` [as here](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) – Martin Smith Dec 26 '13 at 12:42