Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

815 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

9 Experts available now in Live!

Get 1:1 Help Now