create temp table and Store result of execute command at the same time

c#-4.0 entity-framework-6 sql-server tsql


I have a stored procedure where I am running/executing a declared variable which is set to a select query for pivot table, important thing is that that pivot table is transforming column to n number of rows. Yes the rows are not fixed that is why I have problem in creating a temp table with fixed column. What I want is that create a temp table by executing the execute command so the columns would be determined by the select/insert statement. Below is the query.

SELECT @cols = @cols + QUOTENAME(item) + ',' 
FROM (select distinct item from #TableData ) as tmp
select @cols = substring(@cols, 0, len(@cols)) 

set @tequery = 
'SELECT * from 
    select Sno, item, SoldValue from #TableData
) mytbl
    max(ValueInDhs) for GroupName in (' + @cols + ')
) newTable'


I want to store the result of @tequery into a temp table or better this stored procedure should return the result set instead of int as this procedure I have inserted into my entity framework where its showing(after insert) its return type as int.

1/19/2017 12:03:49 PM

Accepted Answer

You could do this with a global (##) temp table, although you'd have to be careful of multiple processes doing this at the same time. Here is an abitrary example:

declare @sql varchar(max) = '
select *
into ##temp -- create global temp table
from   (
          select name
               , create_date = convert(varchar(4), create_date, 102) 
          from   sys.objects) as o
pivot ( count(name) for create_date in ([2017],[2016],[2012], [2009],[2003])
) as p;
exec (@sql)

-- if you do not want to use the global table, select it into a temp table
select * into #temp from ##temp

-- and drop the global
drop table ##temp

select * from #temp
1/19/2017 3:24:52 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow