how to create dynamic table

Question

I have a dynamic pivoted query which generates a result set and I want to insert that data into a table. But the problem is columns are dropped or generated by the time. So by the time I cannot predict columns. That is why I created a dynamic pivoted dataset. So how to insert that data set into table?

One solution is to drop and recreate the table every time but I don't know how to do it. I tried CTE, TEMP table but EXEC support only select, insert, update, delete statement:

DECLARE @columns NVARCHAR(MAX), @sqlquery NVARCHAR(MAX), @orderby Nvarchar(MAX),@value Nvarchar(max);
SET @columns = N'';
SET @value=N'0'
SELECT @columns += N', ' + QUOTENAME([Note_Type])
FROM 
(
    SELECT  distinct 
        No_T
    FROM [DS_DM].[dbo].[DAILY_TABLE]
    where No_T not in (570,80,150,590,80,99)
)as A order by No_T

SET @sqlquery = N'
Select 
K._Number
,D.C_Number
,' + STUFF(@columns, 1, 2, '') + '
from 
(
    select 
        _Number
        ,' + STUFF(@columns, 1, 2, '') + '
    from 
    (
        select distinct  
            right(REPLICATE('+@value+',11) +_Number,11) as [_Number]
            ,No_t
            ,No_T_Des
      FROM [DS_DM].[dbo].[DAILY_TABLE]
      where No_T not in (570,80,150,590,80,99) 
    )AS J
    pivot
    (
    count(No_T_Des) FOR [No_t] IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ') 
    )P 
)K
left join 
[DS_DM].[dbo].[D_TABLE] D on k._Number = D._Number
';

EXEC sp_executesql @sqlquery

how to create dynamic table

2 Answers