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
Solved

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

Posted on 2011-09-16
5
344 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 39
replicated - directional or bidirectional? 3 36
Webservices in T-SQL 3 33
TSQL - How to declare table name 26 42
     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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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