How can I insert dynamic sql data into temp table?

Question

My Dynamic sql return Nth column which is not known but i have to stored this data into #tmp table which is not getting possible.

DECLARE @Columns as VARCHAR(MAX),@SQL VARCHAR(MAX),@SPID VARCHAR(MAX)  
SELECT @Columns =  
COALESCE(@Columns + ', ','') + QUOTENAME(Broker)  
FROM  
(	SELECT DISTINCT Broker  
	FROM tblOutputDetl_csmtuner where Ticker='AHEXY' AND TRIM(Broker)<>''  
) AS B  
ORDER BY B.Broker  
  
SET @SQL ='SELECT * INTO ##Tmp1_'+@SPID+ ' FROM (SELECT * FROM  
(  
	SELECT  
	LineItem,  
	Broker,  
	ItemValue_NoFormat  
	FROM  
	tblOutputDetl_csmtuner where Ticker=''AHEXY''  
) t  
PIVOT(  
	Max(ItemValue_NoFormat)  
	FOR Broker IN ('+@Columns+')  
) AS pivot_table ) x'  
  
  
SELECT * INTO #Tmp1 FROM EXEC(@SQL)  

hence my dynamic sql return non deterministic column that is why i used Select * into #Tmp1 table from FROM EXEC(@alenzi )

but i am getting error.

at last i store the result into Global Temporary table.

here is working code

DECLARE @Columns as VARCHAR(MAX),@SQL VARCHAR(MAX),@SPID VARCHAR(MAX)  
SELECT @Columns =  
COALESCE(@Columns + ', ','') + QUOTENAME(Broker)  
FROM  
(SELECT DISTINCT Broker  
FROM tblOutputDetl_csmtuner where Ticker='AHEXY' AND TRIM(Broker)<>''  
) AS B  
ORDER BY B.Broker  
  
SELECT @SPID=CAST(@@SPID AS VARCHAR)  
EXEC('DROP TABLE IF EXISTS ##Tmp1_'+@SPID)  
  
  
SET @SQL ='SELECT * INTO ##Tmp1_'+@SPID+ ' FROM (SELECT * FROM  
(  
SELECT  
LineItem,  
Broker,  
ItemValue_NoFormat  
FROM  
tblOutputDetl_csmtuner where Ticker=''AHEXY''  
) t  
PIVOT(  
Max(ItemValue_NoFormat)  
FOR Broker IN ('+@Columns+')  
) AS pivot_table ) x'  
  
EXEC(@SQL)  
  
EXEC('select * from ##Tmp1_'+@SPID)  
  
EXEC('DROP TABLE IF EXISTS ##Tmp1_'+@SPID)  

please discuss few ways by which i can store dynamic sql result into #temporary table where i will use select * into instead of insert into #tmp table.

looking for guide line. thanks

Answer 1

Hi @Sudip Bhatt ,

Storing the result into a Global Temporary Table is the best solution for your situation since your dynamic sql returns non deterministic columns.

If you would like to store dynamic sql result into #temporary table or a a table variable, you have to declare the DDL firstly which is not suitable for your situation.

Example of temporary table:

if object_id('tempdb..#t1')  is not null drop table #t1  
  
create table #t1(ID int)  
declare @s varchar(max)  
set @s='insert into #t1(ID)select number from master.dbo.spt_values where type=''P'' and number<10'  
exec(@s)  
  
insert into #t1(id)  
exec('Select 1')  
  
select * from #t1  

Example of table variable:

DECLARE @t TABLE ( id INT )   
  
DECLARE @q NVARCHAR(MAX) = 'declare @t table(id int)   
                            insert into @t values(1),(2)   
                            select * from @t'  
  
INSERT INTO @t  
EXEC(@q)  
  
SELECT * FROM @t  

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.

Answer 2

So if you get this working, how would you then work with the table that has a dynamic name? More dynamic SQL? Don't you see how painful this is getting. Are you one of these people who like to hurt yourself?

A dynamic pivot is a non-relational operation, and there is really only one thing you can do with - return the data to the client. I don't know why you want to save the data in a temp table, but I recommend that you redesign the order of your steps so that you work with data in a relational day as long as you can before you do the dynamic pivot.

And what will you do with the data anyway? Dynamic pivot is a presentational device, and is often best done in the presentation layer.

but i am getting error.

I need to ask: did you try to read the error message? In any case, this is the only advice I can give at this point, since you did not share it with us.

But as I said, you problems started long before you got that error message.