create temp table and Store result of execute command at the same time
c#-4.0 entity-framework-6 sql-server tsql
Question
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
pivot
(
max(ValueInDhs) for GroupName in (' + @cols + ')
) newTable'
execute(@tablequery)
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.
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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
