3

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).

Community
  • 1
  • 1
Tim
  • 8,669
  • 31
  • 105
  • 183

2 Answers2

8

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.

Tombatron
  • 1,567
  • 1
  • 13
  • 27
0
INSERT INTO #MySuperTempTable 
EXEC SP1

Following above code style but sqlserver is not compialing instructions.

Alpesh Gediya
  • 3,706
  • 1
  • 25
  • 38