SQL Server中基于JSON动态生成INSERT语句的技术问询

核心思路 #

先解析JSON提取所有字段，再匹配目标表的列，动态拼接INSERT语句——匹配的列取JSON对应值，未用到的列设为NULL。

具体实现代码 #

DECLARE @data NVARCHAR(MAX) = '{"rows":[{"test_id":11,"score":100,"name":"test"}]}';
DECLARE @tableName NVARCHAR(128) = 'demo_table';
DECLARE @insertCols NVARCHAR(MAX);
DECLARE @valueCols NVARCHAR(MAX);
DECLARE @dynamicSql NVARCHAR(MAX);

-- 1. 提取JSON中的所有键名
DROP TABLE IF EXISTS #JsonKeys;
SELECT DISTINCT [key] AS JsonKey
INTO #JsonKeys
FROM OPENJSON(@data, '$.rows[0]');

-- 2. 生成INSERT的列列表和VALUES列表
SELECT 
    @insertCols = STRING_AGG(QUOTENAME(c.name), ', '),
    @valueCols = STRING_AGG(
        CASE WHEN j.JsonKey IS NOT NULL 
             THEN 'JSON_VALUE(@data, ''$.rows[0].' + j.JsonKey + ''')'
             ELSE 'NULL'
        END, ', '
    )
FROM sys.columns c
LEFT JOIN #JsonKeys j ON c.name = j.JsonKey
WHERE c.object_id = OBJECT_ID(@tableName);

-- 3. 拼接并执行动态SQL
SET @dynamicSql = 'INSERT INTO ' + QUOTENAME(@tableName) + ' (' + @insertCols + ') VALUES (' + @valueCols + ')';
EXEC sp_executesql @dynamicSql, N'@data NVARCHAR(MAX)', @data = @data;

代码说明 #

• 提取JSON键名：用OPENJSON解析JSON的第一行数据，通过DISTINCT去重后存入临时表，确保获取所有需要匹配的字段。
• 匹配表列：从sys.columns获取目标表的所有列，左连接临时表的JSON键名，判断哪些列在JSON中有对应值。
• 动态拼接语句：用STRING_AGG（SQL Server 2017+支持）拼接列名和对应的值表达式，没有匹配的列直接设为NULL。
• 执行动态SQL：用sp_executesql执行拼接好的语句，避免SQL注入风险。

兼容SQL Server 2016及更早版本 #

如果你的SQL Server版本低于2017，无法使用STRING_AGG，可以改用FOR XML PATH来拼接字符串：

-- 替换步骤2的拼接逻辑
SELECT @insertCols = STUFF(
    (SELECT ', ' + QUOTENAME(c.name)
     FROM sys.columns c
     LEFT JOIN #JsonKeys j ON c.name = j.JsonKey
     WHERE c.object_id = OBJECT_ID(@tableName)
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
);

SELECT @valueCols = STUFF(
    (SELECT ', ' + CASE WHEN j.JsonKey IS NOT NULL 
                        THEN 'JSON_VALUE(@data, ''$.rows[0].' + j.JsonKey + ''')'
                        ELSE 'NULL'
                   END
     FROM sys.columns c
     LEFT JOIN #JsonKeys j ON c.name = j.JsonKey
     WHERE c.object_id = OBJECT_ID(@tableName)
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
);

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