dlsimic
asked on
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.
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.
or more specifically, you are only selecting firstname
select firstname
output a.city ...
select firstname
output a.city ...
ASKER
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.
I need a field from the temp table that will not be inserted into the real table to be on the output list.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
insert into Address (fname)