CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
havin
havin

Reputation: 203

Storing the values inside temp table

How to put results of below query in the temp table?

The below query gives me some result set. I need to store the result set in temp table so I can perform some joins with that temp table

Declare @cols nvarchar(max),@query nvarchar(max)
select @cols = 
stuff( ( select  ',[' + Ltrim(rtrim(costelement)) +']' from WECCostElementsCalculation  FOR XML PATH('')),1,1,'');

select @query= 
'with T as 
(select wch.WECCostElementHeaderID,wceyb.WECCostElementsBreakUpID ,wch.WECCostID,WCEYB.ServiceTypeID,WCEYB.WarrantyCoverageID,wecec.CostElement,weceb.Cost
 from  WECCostElementsHeader WCH inner join  WECCostElementsYearBreakUp WCEYB on WCH.WECCostElementHeaderID
 =WCEYB.WECCostElementHeaderID INNER JOIN WECCostElementsBreakUp wecEB on wecEB.WECCostElementsBreakUpID=WCEYB.WECCostElementsBreakUpID  
 left join WECCostElementsCalculation wecec on wecEB.WECCostElementID = wecec.WECCostElementID
 ) 
 select * from T
 PIVOT
 (SUM(Cost) FOR CostElement IN ('+@cols+')) as pvt'

 exec sp_executesql @query

Upvotes: 2

Views: 4997

Answers (3)

gofr1
gofr1

Reputation: 15997

Use SELECT INTO:

select * 
into ##temp 
from T
PIVOT

Then after exec sp_executesql @query

SELECT * FROM ##temp

In case of using temp table you should add on the top of your main query or its dynamic part:

IF OBJECT_ID(N'##temp') IS NOT NULL DROP TABLE ##temp;

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You first need to create the table into which you're going to store the data returned by the stored procedure. Make sure the table has the exact columns as the result set from the stored procedure.

CREATE TABLE #TempTable (WECCostElementHeaderID int, etc. ... )

Now, to insert the data into the table all you need to do is:

INSERT INTO #TempTable
exec sp_executesql @query

Just for quick testing purposes:

create table #TempTable (id int)

declare @query nvarchar(100)

set @query = 'select 1 as id union select 2'

insert into #TempTable
exec sp_executesql @query

select *
from #TempTable

Upvotes: 1

Mr. K
Mr. K

Reputation: 389

you have to create a temporary table and simply put Insert INTO #Temporary_Table(Col1,Col2,....) before exec sp_executesql @query statement. just like below:-

DECLARE @cols NVARCHAR(max)
    ,@query NVARCHAR(max)

SELECT @cols = stuff((
            SELECT ',[' + Ltrim(rtrim(costelement)) + ']'
            FROM WECCostElementsCalculation
            FOR XML PATH('')
            ), 1, 1, '');

CREATE TABLE #Temporary_table(Col1 datatype,Col2 datatype,Col3.....)


SELECT @query = 'with T as 
(select wch.WECCostElementHeaderID,wceyb.WECCostElementsBreakUpID ,wch.WECCostID,WCEYB.ServiceTypeID,WCEYB.WarrantyCoverageID,wecec.CostElement,weceb.Cost
 from  WECCostElementsHeader WCH inner join  WECCostElementsYearBreakUp WCEYB on WCH.WECCostElementHeaderID
 =WCEYB.WECCostElementHeaderID INNER JOIN WECCostElementsBreakUp wecEB on wecEB.WECCostElementsBreakUpID=WCEYB.WECCostElementsBreakUpID  
 left join WECCostElementsCalculation wecec on wecEB.WECCostElementID = wecec.WECCostElementID
 ) 
 select * from T
 PIVOT
 (SUM(Cost) FOR CostElement IN (' + @cols + ')) as pvt'

INSERT INTO #Temporary_table(Col1,Col2,Col3.....)
EXEC sp_executesql @query

SELECT * FROM #Temporary_table

Upvotes: 1

Related Questions