havin havin - 2 months ago 6
SQL Question

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

Answer

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;
Comments