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

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

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?

Solution

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