How to store dynamic sql result into temporary table

Question

This is my dynamic sql.
SET @alenzi = '
Select XX.,'''' scale,Isnull(AllowComma,''FALSE'') AllowComma,Isnull(AllowedDecimalPlace,''0'') AllowedDecimalPlace,
Isnull(AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(CurrencySign,'''') CurrencySign,Isnull(BM_Denominator,'''') BM_Denominator
From
(
---- Broker Detail
Select AA.Section,AA.LineItem,Csm.DisplayInCSM ,AA.BrokerCode Broker,AA.BrokerName,'''' BM_Element,'''' BM_Code,AA.Ord,AA.[Revise Date],AA.LineItemId,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment,1 AS Broker_Data
From tblCSM_ModelDetails Csm LEFT OUTER JOIN (
Select b.,L.ID LineItemId
From #TmpAll_Broker_LI b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
) AA ON Csm.LineItemId=AA.LineItemId
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@Craig _Id))+' AND Csm.BMID=0 AND Type !=''SHEET''
UNION
----- Consensus
Select Section, b.LineItem,DisplayInCSM, '''' Broker,'''' BrokerName,'''' BM_Element,'''' BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment,2 AS Consensus_Data
From #TmpZacksCons b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
INNER JOIN tblCSM_ModelDetails Csm ON Csm.LineItemID=L.ID
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@Craig _Id))+' AND Csm.BMID=0
---- Blue Metrics
UNION
Select Section, b.LineItem,DisplayInCSM,'''' Broker,'''' BrokerName,BM_Element,Code BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment,3 AS BM_Data
From #TmpBM b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
INNER JOIN tblCSM_ModelDetails Csm ON Csm.BMID=b.code AND Csm.LineItemID=L.ID
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@Craig _Id))+'
AND Ord IS NOT NULL
) XX
Left Outer Join tblLiConfig ZZ
On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker='''+@Ticker+'''
Order by ID,Ord,BM_Code,LineItem,BrokerName'

Now how could i store above dynamic sql result data into temporary table?

i should be able to query that temporary table using normal sql instead of dynamic sql.

i have seen i can store the dynamic sql value into Global temporary table like below approach but when i use #temporary table then i am getting error.

declare @query nvarchar(100)  
set @query = N'select * into ##TMPTblTest from tblTest'  
  
exec sp_executesql @query;  
  
select * from ##TMPTblTest  

i want to store above sql result into #temporary using select * into instead of this approach INSERT into #T1 execute ('execute ' + @SQLString ) because my sql will return dynamic columns which is not known at design time.

so please guide me how could i store my dynamic sql result into #temp table using select * into as a result i do not have to create #tmp table with all columns structure.

thanks

Answer 1

A temporary table is dropped when the scope it is created in exits. And a piece of dynamic SQL is a scope of its own.

It seems that you are doing some sort of dynamic pivot, although I did not read your code too carefully. A dynamic pivot is a non-relational operation, and the only thing you can do with is to return the data to the client. You have left the relational world, so you cannot work with the data in a relational database anyway.

Exactly why do think you that want that data in a temp table?

Answer 2

If you need it in some exceptional circumstances, then check the next example. It defines a sample query, then execute it and insert the data to temporary table. The columns are not predefined and are deduced from the dynamic query:

-- sample query  
  
declare @sql nvarchar(max) = 'select 1 as Id, ''John'' as Name, 1234 as Amount union select 2 as Id, ''Sarah'' as Name, 567 as Amount '  
  
-- build destination table '#T1'  
  
drop table if exists #T1  
  
create table #T1([ ] int)  
  
declare @q nvarchar(max)  
  
select @q = string_agg(N'alter table #T1 add ' + quotename([name]) + ' ' + system_type_name + nchar(13) + nchar(10), '') within group (order by column_ordinal)  
from sys.dm_exec_describe_first_result_set(@sql, NULL, 0)  
  
set @q += N'alter table #T1 drop column [ ]' + nchar(13) + nchar(10)  
  
print @q  
  
exec (@q)  
  
-- check the columns  
  
select * from #T1  
  
-- insert data  
  
exec (N'insert #T1 ' + @sql)  
  
-- check the results  
  
select * from #T1  

Perhaps it needs some adjustments for very long @alenzi strings.

Answer 3

Hi @Sudip Bhatt ,

Storing the dynamic sql value into global temporary table is a better solution for your situation since there is no need to drop and create this table every time it runs.

If you insist on using temporary table, you could declare the temporary table structure outside dynamic sql, then you avoid to use global temporary table.

Please refer below example:

if object_id('tempdb..#t1')  is not null drop table #t1  

create table #t1(ID int)  

declare @s nvarchar(max)  
set @s=N'insert into #t1(ID)select number from master.dbo.spt_values where type=''P'' and number<10'  

exec sp_executesql @s;  
  
select * from #t1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Just to add to the others.

It is not very clear in the docs, but temp tables created in sp_executesql are disposed of after the command runs. You must use global temp tables (##tempname) to have them useful outside your sp_executesql code.

SET @sql = '
Select XX.,'''' scale,Isnull(AllowComma,''FALSE'') AllowComma,Isnull(AllowedDecimalPlace,''0'') AllowedDecimalPlace,
Isnull(AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(CurrencySign,'''') CurrencySign,Isnull(BM_Denominator,'''') BM_Denominator
From
(
.....
'


SET @sql = 'DROP TABLE IF EXISTS ##temptable; SELECT * INTO ##temptable FROM (' + @sql + ');'

exec sp_executesql @sql

SELECT * FROM ##temptable;