How to get results of stored procedure #1 into a temporary table in stored procedure #2
Create your temporary table such that it fits the results of your stored procedures.
Assuming your temp. table is called "#MySuperTempTable", you would do something like this...
INSERT INTO #MySuperTempTable (Column1, Column2)
EXEC SP1
That should do the trick.
Tim
Updated on June 05, 2022Comments
-
Tim almost 2 years
I am trying to combine the results of several stored procedures into a single temporary table. The results of the various stored procedures have the same column structure. Essentially, I would like to UNION ALL the results of the various stored procedures. A significant fact: each of the stored procedures creates a temporary table to store its data and the results each returns are based on a select against the temporary table:
create proc SP1 as . . <snip> . select * from #tmp -- a temporary table
Noting that
select * from OPENQUERY(server, 'exec SP1')
does not work if the select in SP1 is against a temporary table (see this question for details), is there another way for a different stored proc, SP2, to get the results of executing SP1 into a temporary table?create proc SP2 as -- put results of executing SP1 into a temporary table: . . .
NOTE: SP1 cannot be modified (e.g. to store its results in a temporary table with session scope).