June 10, 2011 at 2:31 pm
I've found examples out there for everything except the one part I'm trying to do ... so I'm assuming it's not doable but wanted to ask.
Here's the basics of what I'm trying to accomplish:
INSERT INTO TABLE_A (COL1,COL2,COL3)
OUTPUT A.ID,Inserted.ID INTO @tbl (A_ID,B_ID)
SELECT A.COL1, A.COL2, -1*A.COL3
FROM
TABLE_B B
INNER JOIN TABLE_A A ON A.ID = B.ORIGINAL_ID
So, basically, I'm making copies of some rows from TABLE_A. To do this I'm entering the original ID value into TABLE_B and then run that query. After it's all done I need to put into TABLE_B the ID for the new record that was created. As you can see, I was planning on putting that value into a table variable then doing a simple UPDATE.
All fine and dandy except it keeps telling me that "The multi-part identifier "A.ID" could not be bound."
Is there some trick I'm missing ... or can you simply not access columns from the SELECT statement that don't eventually end up in the INSERTED table? (I know it's possible in UPDATE ... FROM statements but the same syntax isn't working here).
TIA.
June 10, 2011 at 2:35 pm
It can't pull those in Insert Select. It's one of those, "What was Microsoft thinking when they did that?" kind of things. Especially since you can get that in updates and deletes.
It's documented in the MSDN article on the Output clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply