SQL Server Insert Result Set Data to temp Table

Question

SQL Server Insert Result Set Data to temp Table

najeeb.uv

SSC Enthusiast

Points: 187
More actions
March 31, 2018 at 3:08 am

#336845

How can i insert the Result set data to tamp table. I got nested stored procedure and need to insert this data to temp table. This is the sample what i am trying to do.

Create Proc GetFinalResult
As
Begin
EXECUTE SomeReport [parameter1],[parameter2]
WITH RESULT SETS
(
(InvID int,
CustomerName,
CustomerMobile,
InvAmount,
));
End

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

najeeb.uv SSC Enthusiast Points: 187 More actions · Answer 1

najeeb.uv - Saturday, March 31, 2018 3:08 AM
How can i insert the Result set data to tamp table. I got nested stored procedure and need to insert this data to temp table. This is the sample what i am trying to do.

Create Proc GetFinalResult
As
Begin
EXECUTE SomeReport [parameter1],[parameter2]
WITH RESULT SETS
(
(InvID int,
CustomerName,
CustomerMobile,
InvAmount,
));
End

Inserted to Temptable but, returns empty table now.

sgmunson SSC Guru Points: 110639 More actions · Answer 2

najeeb.uv - Saturday, March 31, 2018 3:43 AM
najeeb.uv - Saturday, March 31, 2018 3:08 AM
How can i insert the Result set data to tamp table. I got nested stored procedure and need to insert this data to temp table. This is the sample what i am trying to do.

Create Proc GetFinalResult
As
Begin
EXECUTE SomeReport [parameter1],[parameter2]
WITH RESULT SETS
(
(InvID int,
CustomerName,
CustomerMobile,
InvAmount,
));
End
Inserted to Temptable but, returns empty table now.

We can;t do much about the results coming from your stored procedure, but if you want to actually insert the data that the stored procedure produces into a temp table, just using WITH RESULT SETS isn't quite enough. If you know the columns and data types, you don't necessarily need WITH RESULT SETS. You do need, however, to code an INSERT INTO statement immediately prior to the EXECUTE statement to identify what table to put the data in, so if a temp table is needed, you'll also have to create it as well. WITH RESULT SETS only guarantees the column names and data types of the result set(s). It will not place the data into a table for you.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)