Solved

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

Posted on 2011-09-16
5
341 Views
Last Modified: 2012-05-12
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
Comment
Question by:dlsimic
5 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36552022
is it because you are only inserting one column - fname - and it is not a.city?

insert into Address (fname)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36552025
or more specifically, you are only selecting firstname

select firstname
output a.city ...
0
 

Author Comment

by:dlsimic
ID: 36552036
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36553187
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 36555214
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now