Solved

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

Posted on 2011-09-16
5
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 60

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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