如何在T-SQL中根据动态SQL语句的执行结果创建新表

报错根因说明 #

1. 关键字'EXECUTE'附近存在语法错误：
   • 第一类原因是执行动态SQL时未加括号：SQL Server 中执行动态字符串的语法是EXEC (@query)，括号不可省略，省略括号会被识别为调用存储过程而非执行动态SQL，触发语法错误
   • 第二类原因是语法误用：SQL Server 不支持CREATE TABLE 表名 AS EXEC(@query)这类写法，不能直接将EXEC的执行结果绑定到建表语句上
   • 另外你原有代码的SQL拼接存在空格缺失问题：@cols和后面的FROM之间没有空格，拼接后会生成xxxFROM的错误语法，也会触发执行报错
2. 必须声明变量@query：
   你声明的@cols、@query变量作用域仅限当前批处理，如果在变量声明和执行语句之间加了GO关键字，或者拆分到不同的查询窗口执行，就会触发变量未声明的报错。

解决方案 #

场景1：直接根据查询结果创建新表（无需提前建表） #

直接将SELECT INTO逻辑写入动态SQL的拼接内容中即可，完整可运行代码如下：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 可选：如果要重复运行，先判断删除已存在的目标表
IF OBJECT_ID('[Sandbox].[dbo].[你的目标表名]', 'U') IS NOT NULL
DROP TABLE [Sandbox].[dbo].[你的目标表名];

SET @cols = STUFF((SELECT DISTINCT
                       ',' + QUOTENAME(c.[ClassCode])
                   FROM [Sandbox].[dbo].Test2 c 
                   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');

-- 调整了拼接的空格，同时增加INTO语句
SET @query = 'SELECT [Name_ID], ' + @cols + 
             ' INTO [Sandbox].[dbo].[你的目标表名] ' +
             ' FROM (SELECT [Name_ID],
                           [ClassCodeYN] AS [amount],
                           [ClassCode] AS [category]
                    FROM [Sandbox].[dbo].[Test2]) x 
              PIVOT (COUNT(amount) FOR category IN (' + @cols + ')) p';

-- 执行动态SQL必须加括号
EXECUTE (@query)

执行完成后，[Sandbox].[dbo].[你的目标表名]就是你需要的PIVOT后的结果表。

场景2：插入到已存在的目标表 #

如果已经提前建好结构匹配的目标表，使用INSERT INTO + EXEC的写法即可：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT
                       ',' + QUOTENAME(c.[ClassCode])
                   FROM [Sandbox].[dbo].Test2 c 
                   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');

SET @query = 'SELECT [Name_ID], ' + @cols + 
             ' FROM (SELECT [Name_ID],
                           [ClassCodeYN] AS [amount],
                           [ClassCode] AS [category]
                    FROM [Sandbox].[dbo].[Test2]) x 
              PIVOT (COUNT(amount) FOR category IN (' + @cols + ')) p';

-- 插入到已存在的表，注意字段数量、类型必须和查询结果完全匹配
INSERT INTO [Sandbox].[dbo].[你提前建好的目标表名]
EXECUTE (@query)

注意事项 #

• 不要在变量声明和EXEC执行语句之间添加GO批处理分隔符，避免变量作用域失效
• 提前建表插入的场景下，需要保证表的字段顺序、字段类型、字段数量和PIVOT后的查询结果完全一致，否则会触发插入报错

内容的提问来源于stack exchange，提问作者Edward Osorio