How can I store a temporary table from an executed procedure?

Question

I have the following code:

SELECT @Name = [TrigTable] 
FROM [dbo].[setdevelopmentjob] 
WHERE [TrigTable] IS NOT NULL

PRINT @Name

SET @sql = 'SELECT * FROM ' + @Name;

#TriggerTable = EXEC sp_executesql @sql;

SELECT * FROM #TriggerTable

Obviously the line #TriggerTable = Exec sp_executesql @sqlis incorrect syntax but it shows what I'm trying to do. The columns are variable, meaning that I can't just declare a table variable. How can I pass the output of this executed procedure to #TriggerTable?

You may want to look at this : https://stackoverflow.com/questions/7246276/sql-server-put-stored-procedure-result-set-into-a-table-variable-without-specif — Harry, Mar 20 '19 at 23:39
use OPENROWSET, best way to treat the results of a stored procedure as a table expression — CR241, Mar 20 '19 at 23:44
Not quite. I've tried these approaches. when you run sp_executesql you can't retrieve any temp tables that are produced from the query — Joshua Jack, Mar 20 '19 at 23:44
Didn't any of the answers listed here help you? https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — Sateesh Pagolu, Mar 20 '19 at 23:46

score 3 · Accepted Answer · edited Mar 21 '19 at 03:38

You can store the data in Global temporary table (##) with Select * into approach and to store in #temp table you have to create the table first which I am aware of while using dynamic sql But you can certainly do that in run time but still you may need some physical table to access it.

create table testtmp (id int, namen varchar(15)) 

--inserting the data into physical table 
insert into testtmp (id, namen)

select 1 as ID, 'XYZ' as namen union all
select 2 as ID, 'ABC' as namen union all
select 3 as ID, 'DIG' as namen

create table #temp (ID int) 

declare @sql nvarchar(max) = 'select ID from testtmp' 
insert into #temp exec sp_executesql @sql 

select * from #temp 

Gives you this output: 

 ID 
  1 
  2 
  3

With global temporary table you can do it easily and you don't have to create any tables, you can specify column names if you would like to.

 declare @sql nvarchar(max) = 'select * into ##Gloabltmptest from testtmp' 
 exec sp_executesql @sql 

 select * from ##Gloabltmptest

Output:

 ID  namen
 1  XYZ
 2  ABC
 3  DIG

Added table variable as well, similar to #temp tables.

declare @table table (IDtab int, nametab varchar(15)) 

declare @sql nvarchar(max) = 'select * from testtmp' 
insert into @table exec sp_executesql @sql 

select * from @table

How can I store a temporary table from an executed procedure?

1 Answers1