如何在SQL Server中动态创建表并插入存储过程结果集？

针对你需要根据存储过程动态结果集创建表并插入数据的需求，SQL Server里可以通过以下两种方案实现：

方案1：使用OPENROWSET + SELECT INTO（推荐，直接高效） #

这种方法可以直接基于存储过程的输出结果创建新表，自动匹配列名和数据类型。

步骤1：先启用Ad Hoc Distributed Queries配置（仅首次执行） #

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

步骤2：执行动态创建表并插入数据的语句 #

假设你的存储过程名为YourStoredProcedure，要创建的新表名为NewDynamicTable，执行以下SQL：

SELECT *
INTO NewDynamicTable
FROM OPENROWSET('SQLNCLI', 'Server=你的SQL服务器名;Trusted_Connection=yes;',
     'EXEC 你的数据库名.dbo.YourStoredProcedure') AS temp;

注意事项：

• 替换语句中的你的SQL服务器名、你的数据库名、YourStoredProcedure为实际信息
• 如果存储过程需要传入参数，直接在EXEC后添加参数，比如EXEC 你的数据库名.dbo.YourStoredProcedure @Param1='值1', @Param2=2
• 执行账号需要有足够的权限（比如ADMINISTER BULK OPERATIONS权限，以及存储过程的执行权限）

方案2：用动态SQL获取结果集元数据后创建表（适合复杂场景） #

如果方案1的配置受限，可以先通过存储过程的执行结果获取列信息，再动态生成建表语句并插入数据：

步骤1：将存储过程结果插入临时表 #

CREATE TABLE #TempResult (ID INT IDENTITY(1,1)) -- 先创建一个临时表，后续会自动扩展列
INSERT INTO #TempResult
EXEC 你的数据库名.dbo.YourStoredProcedure;

执行后临时表会自动扩展为存储过程结果集的完整列结构。

步骤2：动态生成建表语句并创建正式表 #

DECLARE @CreateTableSQL NVARCHAR(MAX)
SET @CreateTableSQL = 'CREATE TABLE NewDynamicTable (' + 
    STUFF((SELECT ', ' + QUOTENAME(COLUMN_NAME) + ' ' + DATA_TYPE + 
        CASE WHEN DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') THEN '(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ')'
             WHEN DATA_TYPE IN ('decimal', 'numeric') THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
             ELSE '' END
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_NAME = '#TempResult'
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'

EXEC sp_executesql @CreateTableSQL

步骤3：将临时表数据插入正式表 #

INSERT INTO NewDynamicTable
SELECT * FROM #TempResult

DROP TABLE #TempResult -- 清理临时表

补充说明 #

你之前用的CREATE TABLE new_tbl BY SELECT * FROM ...是MySQL专属语法，SQL Server中对应的基础写法是SELECT * INTO new_tbl FROM ...，但针对存储过程的结果集，直接用该语法无法实现，必须结合OPENROWSET或者临时表中转来完成动态建表。

内容的提问来源于stack exchange，提问作者pramod zirale