sql - 如何创建动态表

Question

我有一个生成结果集的动态透视查询，我想将该数据插入到表中。但问题是列被删除或生成的时间。所以到时候我无法预测列。这就是我创建动态旋转数据集的原因。那么如何将该数据集插入到表中呢？

一种解决方案是每次都删除并重新创建表，但我不知道该怎么做。我尝试了 CTE、TEMP 表，但 EXEC 仅支持 select、insert、update、delete 语句:

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

Answer 1

我已经修改了您的代码以反射(reflect)我提出的解决方案。

IF OBJECT_ID (N'NEW_TABLE', N'U') IS NOT NULL 
BEGIN 
DROP TABLE NEW_TABLE 
END 


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, '') + '

INTO NEW_TABLE 
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