• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

output clause of insert statement; selecting variables of the from table

I have been able to successfully select variables of the from table in an update/output clause but have not been able to during an insert.

Example of update working (I am able to use a.city in the output list):
 
update Address
set CountyID=a.CountyID
output a.city, inserted.AddressID into @addressoutput (somekey, AddressID)
from @address a
inner join address b on a.City=b.city

However, I cannot get this to work during an insert statement:

insert into Address (fname)
output a.city, inserted.AddressID into @addressoutput (somekey, AddressID)
      select firstname
      from @address a

The error I get is :
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "a.city" could not be bound.

Is this possible during an insert / output senerio?

I need to store a temp key used in the @address table and the matching identity column from the insert to the Address table.  This will prevent having to cursor through the inserts to get this.

Thanks for your help.
0
dlsimic
Asked:
dlsimic
2 Solutions
 
knightEknightCommented:
is it because you are only inserting one column - fname - and it is not a.city?

insert into Address (fname)
0
 
knightEknightCommented:
or more specifically, you are only selecting firstname

select firstname
output a.city ...
0
 
dlsimicAuthor Commented:
The example only had one column as to keep it short.

I need a field from the temp table that will not be inserted into the real table to be on the output list.
0
 
Kevin CrossChief Technology OfficerCommented:
I am not sure that is possible. It is the only scenario not listed in the BOL on OUTPUT. The fact that it goes through each case individually but skips this one makes me strongly feel it is not supported. That coupled with the fact that the OUTPUT happens AFTER the INSERT. However, given this works with UPDATE, I will have to test a few things...
0
 
LowfatspreadCommented:
in the update case the table A is within the scope of the update statement....

however with the insert the table A is not within the scope of the Insert ... its part of the Select "subquery"

i think the only way you could achieve this is if you create a view which allows you to present the extra column to
the insert statement and have an instead of trigger to actually perform the insert... that way the extra column could be included in the ouptu statement...
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now