如何让SQL Server中基于OPENJSON的INSERT遇错后继续执行？

问题描述 #

正如预期，以下使用OPENJSON的代码示例会在SQL Server表中抛出主键冲突错误，因为id列重复插入了值2，且所有记录都不会被插入。

需求：修改代码，使其遇到错误时忽略有问题的记录（比如重复的2），继续插入其他合法值，就像逐条执行INSERT语句的示例那样。

说明：主键冲突仅为演示场景，实际需要处理基于JSON的INSERT操作中所有可能的SQL错误（如数据类型不匹配等），确保合法记录正常插入。

原代码示例 #

使用OPENJSON的示例【遇错则无任何记录插入】

CREATE TABLE #t
(
    id int NOT NULL, 
    CONSTRAINT PK_tmpTbl_ID PRIMARY KEY CLUSTERED (id)
)

DECLARE @json nvarchar(max);
SET @json = N'[
 { "id" : 1},
 { "id" : 2},
 { "id" : 3},
 { "id" : 2},
 { "id" : 5}
]';

INSERT INTO #t (id)
    SELECT id
    FROM OPENJSON(@json)
       WITH (id int)

常规INSERT示例【错误记录被忽略，其余插入成功】

insert into #t(id) values(1)
insert into #t(id) values(2)
insert into #t(id) values(3)
insert into #t(id) values(2) -- 抛出错误并被忽略
insert into #t(id) values(5)

预期结果

解决方案 #

方案1：处理主键/唯一键冲突（高效批量插入）

如果仅需忽略主键或唯一键的重复值，可通过以下两种方式实现：

方法A：修改主键的IGNORE_DUP_KEY属性

创建主键时开启IGNORE_DUP_KEY，批量插入时遇到重复值只会触发警告而非报错，合法记录正常插入：

CREATE TABLE #t
(
    id int NOT NULL, 
    CONSTRAINT PK_tmpTbl_ID PRIMARY KEY CLUSTERED (id) WITH (IGNORE_DUP_KEY = ON)
)

DECLARE @json nvarchar(max);
SET @json = N'[
 { "id" : 1},
 { "id" : 2},
 { "id" : 3},
 { "id" : 2},
 { "id" : 5}
]';

INSERT INTO #t (id)
    SELECT id
    FROM OPENJSON(@json)
       WITH (id int)

执行后会返回警告“违反了 PRIMARY KEY 约束...已跳过重复的键”，但1、2、3、5会成功插入。

方法B：用WHERE NOT EXISTS过滤已存在的记录

若不想修改主键属性，可先过滤掉目标表中已存在的id：

CREATE TABLE #t
(
    id int NOT NULL, 
    CONSTRAINT PK_tmpTbl_ID PRIMARY KEY CLUSTERED (id)
)

DECLARE @json nvarchar(max);
SET @json = N'[
 { "id" : 1},
 { "id" : 2},
 { "id" : 3},
 { "id" : 2},
 { "id" : 5}
]';

INSERT INTO #t (id)
    SELECT j.id
    FROM OPENJSON(@json) WITH (id int) j
    WHERE NOT EXISTS (SELECT 1 FROM #t t WHERE t.id = j.id)

该方式无警告，直接插入所有不存在的记录。

方案2：处理通用错误（如数据类型不匹配）

若需处理更广泛的错误（比如JSON中的id是字符串无法转为int），可结合TRY_CAST/TRY_CONVERT解析JSON并过滤合法数据：

CREATE TABLE #t
(
    id int NOT NULL, 
    CONSTRAINT PK_tmpTbl_ID PRIMARY KEY CLUSTERED (id)
)

DECLARE @json nvarchar(max);
SET @json = N'[
 { "id" : 1},
 { "id" : "abc"}, -- 无法转为int的非法值
 { "id" : 3},
 { "id" : 2},
 { "id" : 5}
]';

INSERT INTO #t (id)
    SELECT TRY_CAST(j.id AS int)
    FROM OPENJSON(@json) WITH (id nvarchar(50)) j -- 先按字符串解析
    WHERE TRY_CAST(j.id AS int) IS NOT NULL -- 过滤转换失败的记录
    AND NOT EXISTS (SELECT 1 FROM #t t WHERE t.id = TRY_CAST(j.id AS int)) -- 同时过滤重复值

此方法既忽略数据类型错误的记录，也跳过重复主键值，合法记录正常插入。

方案3：逐行处理（兼容所有错误场景）

若需处理所有可能的错误（约束冲突、数据类型错误等），可先将JSON数据存入表变量，再通过循环+TRY...CATCH逐行插入：

CREATE TABLE #t
(
    id int NOT NULL, 
    CONSTRAINT PK_tmpTbl_ID PRIMARY KEY CLUSTERED (id)
)

DECLARE @json nvarchar(max);
SET @json = N'[
 { "id" : 1},
 { "id" : 2},
 { "id" : 3},
 { "id" : 2},
 { "id" : "abc"}
]';

-- 先将JSON解析到表变量
DECLARE @tempTable TABLE (id nvarchar(50), rowNum int IDENTITY(1,1))
INSERT INTO @tempTable(id)
SELECT id FROM OPENJSON(@json) WITH (id nvarchar(50))

DECLARE @currentRow int = 1, @maxRow int = (SELECT MAX(rowNum) FROM @tempTable)
DECLARE @currentId int

WHILE @currentRow <= @maxRow
BEGIN
    BEGIN TRY
        SELECT @currentId = TRY_CAST(id AS int) FROM @tempTable WHERE rowNum = @currentRow
        IF @currentId IS NOT NULL -- 确保数据类型合法
        BEGIN
            INSERT INTO #t(id) VALUES(@currentId)
        END
    END TRY
    BEGIN CATCH
        -- 可选：在此记录错误信息，比如插入错误日志表
        -- INSERT INTO ErrorLog (ErrorMessage, ErrorTime) VALUES(ERROR_MESSAGE(), GETDATE())
    END CATCH
    SET @currentRow = @currentRow + 1
END

该方式逐行尝试插入，任何错误都会被捕获并跳过，合法记录正常插入，还可在CATCH块中记录错误详情。

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