May 20, 2008 at 3:57 pm
I want to return more data from my source than is actually getting inserted. Found
this post, and it's close, but not quite.
INSERT [subAppeals_cases](
[revID],
[reportID],
[reportDate]
)
OUTPUTx.[rID]
INSERTED.[rID]
INTO @caseTbl
SELECT @revID,x.[caseRecord],x.[caseDate],x.[rID]
FROM #xmlResults x
In the above example (though it's not real clear), what I'm putting into @caseTbl is two id fields. I want one from the inserted table, and one from the source. However, I can't seem to bind outside the 'output' clause. Any idea how I can pass the data along without actually using it?
May 20, 2008 at 4:21 pm
I'm not sure I follow - you're outputting into a table variable, which survives that actual statement, allowing you to do pretty much anything you want with that data. What can you not do?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 4:37 pm
match rows. I mean, I suppose I *can*, but it means joining back on like four or five columns to be unique.
May 20, 2008 at 4:41 pm
Oblio Leitch (5/20/2008)
match rows. I mean, I suppose I *can*, but it means joining back on like four or five columns to be unique.
Again - not nearly enough info to help. Match rows...to what? based on what? Are you looking for more data out of one of the tables already involved in the query? What do these tables look like?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 4:48 pm
If you're talking about accessing data from the base table not used in the insert...unfortunately - you have to relink to it somehow.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2008 at 6:13 am
Argh! Stops just short of being useful!
May 21, 2008 at 6:18 am
You can't join outside the OUTPUT clause, but since you've put the records into a table variable (you might want to use a temp table for this instead) you can then join from the table variable back to the table that got updated as a second select statement. Won't that do what you need? Something like this:
INSERT [subAppeals_cases](
[revID],
[reportID],
[reportDate]
)
OUTPUT x.[rID]
INSERTED.[rID]
INTO @caseTbl
SELECT ...
FROM @caseTbl c
JOIN subAppeals_Cases s
ON c.rID = s.rID
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply