使用OPENJSON插入动态表时，如何在WITH子句中指定动态列名？

你遇到的这个情况很典型——OPENJSON的WITH子句本身是静态的，没法直接用变量替换列名，不过咱们可以通过动态拼接SQL语句来实现需求，核心思路是先获取目标表的列结构（或者你提前维护的JSON路径映射），然后把列名、数据类型和对应的JSON路径拼进WITH子句里，最后执行动态生成的SQL。

我结合你的例子给你写个完整的实现方案：

1. 先明确前提 #

假设你的Students表有id、fname、surname这三个列，同时为了处理嵌套JSON（比如你例子里的info.fname），咱们可以维护一个列-JSON路径映射表，用来指定每个表的列对应JSON里的哪个路径：

-- 先创建映射表（只需要创建一次）
CREATE TABLE ColumnJsonMapping (
    TableName NVARCHAR(128) NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL,
    JsonPath NVARCHAR(256) NOT NULL,
    PRIMARY KEY (TableName, ColumnName)
)

-- 插入Students表的映射关系
INSERT INTO ColumnJsonMapping VALUES 
('Students', 'id', '$.id'),
('Students', 'fname', '$.info.fname'),
('Students', 'surname', '$.info.surname')

2. 动态生成SQL并执行 #

接下来就是写动态SQL的逻辑，步骤是：

• 从JSON里提取目标表名
• 从映射表和系统表中获取列名、数据类型和对应的JSON路径
• 拼接出完整的OPENJSON WITH子句
• 生成并执行最终的INSERT语句

完整代码：

DECLARE @jsonVariable NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @WithClause NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 你的测试JSON
SET @jsonVariable = N'{ "id" : "12","info": { "fname": "John", "surname": "Smith" },"table":"Students" }'

-- 提取目标表名
SET @TableName = JSON_VALUE(@jsonVariable, '$.table')

-- 动态生成OPENJSON的WITH子句内容
SELECT @WithClause = STRING_AGG(
    CONCAT(
        QUOTENAME(c.ColumnName), ' ', t.name, ' ''', c.JsonPath, ''''
    ),
    ', '
)
FROM ColumnJsonMapping c
JOIN sys.columns col 
    ON col.object_id = OBJECT_ID(c.TableName) 
    AND col.name = c.ColumnName
JOIN sys.types t 
    ON col.system_type_id = t.system_type_id
WHERE c.TableName = @TableName

-- 拼接完整的INSERT语句
SET @SQL = CONCAT(
    'INSERT INTO ', QUOTENAME(@TableName), ' ',
    'SELECT * FROM OPENJSON(@json) ',
    'WITH (', @WithClause, ')'
)

-- 执行动态SQL，注意用sp_executesql传递参数防止SQL注入
EXEC sp_executesql 
    @SQL, 
    N'@json NVARCHAR(MAX)', 
    @json = @jsonVariable

关键注意点 #

• 避免SQL注入：一定要用QUOTENAME()包裹表名和列名，同时用sp_executesql传递JSON参数，不要直接把JSON内容拼进SQL里。
• 嵌套JSON处理：通过映射表可以灵活指定任何层级的JSON路径，比如$.info.contact.phone这种深层路径都能处理。
• 数据类型匹配：从sys.types获取目标表的列类型，确保JSON解析出来的类型和表列类型一致，避免插入报错。

如果你的JSON键和表列名完全一致（没有嵌套），那可以不用映射表，直接从sys.columns生成路径（比如$.ColumnName），这样更简单。

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