在sql server 2022中,通过json数据直接批量insert的最佳做法是使用openjson函数配合values表值构造函数。具体而言,将json数组作为字符串参数传递给openjson,利用其返回的行集结果插入目标表。这样做可以将多行json数据一次性转换为关系型数据并执行单条insert语句,避免了逐行处理的开销。

如何解析单层JSON数组并插入表
对于结构简单的JSON数组,可以直接使用OPENJSON的默认模式进行解析。该模式假设JSON对象包含键值对,并将其映射为表的列。只需提供JSON字符串和可选的列定义(WITH子句),即可快速完成数据提取和插入。

CREATE TABLE #TargetTable (Id INT, Name NVARCHAR(50), Value DECIMAL(10,2));
DECLARE @JsonData NVARCHAR(MAX) = N'
[
{"Id": 1, "Name": "Item A", "Value": 10.5},
{"Id": 2, "Name": "Item B", "Value": 20.0}
]';
INSERT INTO #TargetTable (Id, Name, Value)
SELECT Id, Name, Value
FROM OPENJSON(@JsonData)
WITH (
Id INT,
Name NVARCHAR(50),
Value DECIMAL(10,2)
);运行上述代码后,#TargetTable中将包含两行数据:Id为1和2,对应的Name和Value也正确映射。如果JSON中的字段名与表列名不一致,必须在WITH子句中显式指定映射关系。
如何处理嵌套JSON对象的批量插入
当JSON数据包含嵌套对象或数组时,需要使用OPENJSON的显式模式(Explicit Mode)或递归CTE来处理。通常的做法是先解析外层数组,再对内层对象进行二次解析。这种方法虽然稍微复杂,但能确保数据的完整性和准确性。
exa-js 是 Exa 官方 JavaScript 软件开发工具包,Exa 是一个为 AI 工作流构建的网络搜索 API。它为代理提供了一种具体的方式来搜索网络、检索页面内容,并从代码中生成有来源的答案,而不是手动拼接原始 HTTP 调用。

CREATE TABLE #NestedTarget (OuterId INT, InnerKey NVARCHAR(50), InnerValue NVARCHAR(100));
DECLARE @NestedJson NVARCHAR(MAX) = N'
[
{
"OuterId": 1,
"InnerData": {
"Key": "A1",
"Value": "Val1"
}
},
{
"OuterId": 2,
"InnerData": {
"Key": "B2",
"Value": "Val2"
}
}
]';
INSERT INTO #NestedTarget (OuterId, InnerKey, InnerValue)
SELECT
o.OuterId,
i.InnerKey,
i.InnerValue
FROM OPENJSON(@NestedJson) AS o
CROSS APPLY OPENJSON(o.[value])
WITH (
OuterId INT,
InnerData NVARCHAR(MAX) AS JSON
) AS outerParsed
CROSS APPLY OPENJSON(outerParsed.InnerData)
WITH (
InnerKey NVARCHAR(50),
InnerValue NVARCHAR(100)
) AS i;执行此脚本后,#NestedTarget表将包含两行记录,分别对应两个嵌套对象中的InnerKey和InnerValue。这种CROSS APPLY的方式能够有效地展开嵌套结构。
基于输入JSON中缺失字段时的默认值处理
在实际应用中,输入的JSON数据可能缺少某些字段。OPENJSON允许在WITH子句中为每个列指定DEFAULT值或使用COALESCE等函数处理NULL。如果JSON中缺少某个键,OPENJSON返回NULL,此时可以通过设置DEFAULT来填充预定义的值,从而保证插入数据的完整性。
-- 假设JSON中缺少Value字段
DECLARE @IncompleteJson NVARCHAR(MAX) = N'[{"Id": 3, "Name": "Item C"}]';
INSERT INTO #TargetTable (Id, Name, Value)
SELECT
Id,
Name,
COALESCE(Value, 0.0) AS Value -- 如果Value为NULL,则默认为0.0
FROM OPENJSON(@IncompleteJson)
WITH (
Id INT,
Name NVARCHAR(50),
Value DECIMAL(10,2)
);在此示例中,由于JSON对象中没有Value键,OPENJSON返回的Value列为NULL。通过COALESCE函数,我们将NULL转换为0.0,确保插入到#TargetTable中的Value列有一个有效的默认值。这避免了因缺失字段导致的插入错误或数据不一致。

















