Insert dynamic pivot data into a #tmp table rather than a ##tmp table

Thanks but because it's a dynamic pivot I will not know the table structure. The pivot columns are a complete unknown.

Hi @moondaddy ,

You could declare the temporary table structure outside dynamic sql, then avoid to use global temporary table.

Please refer below example and check whether it is helpful to you.

create table testtable  
(ID int,Name varchar(10))  
  
insert into testtable values (1,'A')  
  
if object_id('tempdb..#Test')  is not null drop table #Test  
  
create table #Test (ID int,Name varchar(10))   
  
declare @query nvarchar(max)  
  
set @query = N'   
insert into #Test select * from testtable  
 '  
exec sp_executesql @query;  
  
select * from #Test  

But it could be better for you to provide table CREATE TABLE statements for your tables together with INSERT statements with sample data, your dynamic pivot statement, enough to illustrate all angles of the problem.

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.