SQL批量解析嵌套JSON至多表的错误处理方案咨询

问题背景 #

将嵌套JSON结构解析至appointment、dock、warehouse三张SQL表，使用OPENJSON完成批量插入与更新操作。当所有JSON数据有效时，批量解析速度极快；但只要单条数据存在问题（如数据类型转换错误，测试中出现将nvarchar值'error'转换为datetime类型失败的错误），由于事务机制，整个事务会回滚，所有记录均无法解析。

当前处理代码（以Appointment表为例） #

UPDATE a
SET a.start = ss.start
FROM dbo.Appointment a
CROSS APPLY
OPENJSON(a.payload, '$')
WITH (id UNIQUEIDENTIFIER,
      start DATETIME) ss

INSERT dbo.Appointment_Dock
(
    ...
)
SELECT ...
FROM dbo.Appointment a
CROSS APPLY
OPENJSON(a.payload, '$.dock')
WITH (id UNIQUEIDENTIFIER,
      ...) ss

测试用JSON示例 #

{
    "data": [
        {
            "id": "4940b7b9-7b30-42e7-8a0f-63f4095c75f4",
            "start": "2023-11-17T20:00:00.000Z",
            "dock": {
                "id": "ca009877-6559-4fb2-afa7-d8c34e9cd819",
                "name": "Door# 1",
                "warehouseId": "30e9ad49-4faf-4d40-805c-9dc75e67d873",
                "warehouse": {
                    "id": "30e9ad49-4faf-4d40-805c-9dc75e67d873",
                    "name": "Warehouse A"
                }
            }
        },
        {
            "id": "4940b7b9-7b30-42e7-8a0f-63f4095c75f4",
            "start": "2023-11-18T20:00:00.000Z",
            "dock": {
                "id": "ca009877-6559-4fb2-afa7-d8c34e9cd819",
                "name": "Door# 1",
                "warehouseId": "30e9ad49-4faf-4d40-805c-9dc75e67d873",
                "warehouse": {
                    "id": "30e9ad49-4faf-4d40-805c-9dc75e67d873",
                    "name": "Warehouse A"
                }
            }
        }
    ],
    "entity": "Appointment",
    "action": "read"
}

需求与疑问 #

• 逐条处理虽能保留有效记录、标记错误记录，但速度过慢，希望在SQL中实现批量处理，让有效记录正常入库，错误记录仅标记错误信息。
• 是否适合采用CLR实现此需求？或有无其他解决方案（不想用C#处理以避免网络传输开销）？

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