How can I insert dynamic sql data into temp table?

Question

question

SudipBhatt-9737 asked Feb 3, '21 TomislavDunaj-7525 commented Jul 27, '22

How can I insert dynamic sql data into temp table?

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(@SQL)

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

sql-server-transact-sql

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 · Feb 03, 2021 at 10:43 PM

I think the code using the global temporary table works. What is the error you got?

0 ·

Answer 1 · 2021-02-04T02:15:08.09Z

MelissaMa-msft answered Feb 4, '21 TomislavDunaj-7525 commented Jul 27, '22

Hi @SudipBhatt-9737,

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.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Satya-1922 · Mar 18 at 03:44 AM

Thank you Melissa for sharing this solution!

Satya

0 ·

TomislavDunaj-7525 · Jul 27 at 12:51 PM

Thanks, Melissa!
Good & simple solution, as it should be.

Best regards,
Tom

0 ·

Answer 2 · 2021-02-03T22:47:32.187Z

ErlandSommarskog answered Feb 3, '21

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question