SQL Server存储过程中JSON加载数据时CROSS APPLY重复问题解决

我需要通过SQL Server存储过程填充两张主键自动生成的表，传入JSON数组作为数据输入。生成PARENT表后，需将其新增的ID作为PRODUCT表每条记录的外键。当前实现的存储过程使用CROSS APPLY时，PRODUCT表会产生与数组大小一致的重复记录，请问如何让PRODUCT表仅添加单条记录，或有无无需使用CROSS APPLY的实现方案？

原存储过程代码 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CREATE_PRODUCT]
    @parentJSONData NVARCHAR(MAX)
AS
BEGIN
BEGIN TRANSACTION;
    SAVE TRANSACTION StartPoint;
    DECLARE @PARENT TABLE ([ID] INT, [PARENT_NAME] NVARCHAR(50), [EFFECTIVE_FROM_WEEK] INT, [EFFECTIVE_TO_WEEK] INT, [ADD_FLAG] INT, [DEL_FLAG] INT)
    DECLARE @PRODUCT TABLE ([PRODUCT_CODE_ID] INT, [PRODUCT_STATUS_ID] INT, [PRODUCT_NAME] NVARCHAR(50), [PARENT_ID] INT NOT NULL)
    DECLARE @Output TABLE ([ID] INT,[PARENT_NAME] NVARCHAR(50), [EFFECTIVE_FROM_WEEK] INT)

    DECLARE @ErrorMessage  nvarchar(MAX)
    DECLARE @ErrorSeverity nvarchar(MAX)
    DECLARE @ErrorState    nvarchar(MAX)
BEGIN TRY
INSERT INTO PARENT 
        ([PARENT_NAME], [EFFECTIVE_FROM_WEEK], [EFFECTIVE_TO_WEEK], [ADD_FLAG], [DEL_FLAG]) OUTPUT inserted.ID, inserted.PARENT_NAME, inserted.EFFECTIVE_FROM_WEEK INTO @Output
SELECT [PARENT_NAME], [EFFECTIVE_FROM_WEEK], [EFFECTIVE_TO_WEEK], [ADD_FLAG], [DEL_FLAG]
FROM OPENJSON(@parentJSONData)  WITH (
    PARENT_NAME      NVARCHAR(50) '$.parent.parentName'
        , EFFECTIVE_FROM_WEEK INT '$.parent.effectiveFromWeek'
        , EFFECTIVE_TO_WEEK   INT '$.parent.effectiveToWeek'
        , ADD_FLAG        INT '$.parent.addFlag.id'
        , DEL_FLAG        INT '$.parent.delFlag.id'
    ) AS jsonValues;

INSERT INTO PRODUCT
([PRODUCT_CODE_ID], [PRODUCT_STATUS_ID], [PRODUCT_NAME], [PARENT_ID])
SELECT PRODUCT_CODE_ID, PRODUCT_STATUS_ID, PRODUCT_NAME, ID
FROM OPENJSON(@parentJSONData) WITH (
    PRODUCT_CODE_ID      NVARCHAR(50) '$.productCodeID'
        , PRODUCT_STATUS_ID INT '$.productStatusID'
        , PRODUCT_NAME   NVARCHAR(50) '$.productName'
    ) AS jsonValues CROSS APPLY @Output;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
        SET @ErrorMessage  = ERROR_MESSAGE()
        SET @ErrorSeverity = ERROR_SEVERITY()
        SET @ErrorState    = ERROR_STATE()
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END;
GO

输入JSON #

[{"parent":{"parentName":"Paper Pack","effectiveFromWeek":202423,"effectiveToWeek":202534,"addFlag":{"id":1,"state":"4x4"},"delFlag":{"id":1,"state":"4x4"}},"productCodeID":23386,"productStatusID":71013,"productName":"Paper Package"},{"parent":{"parentName":"Pen Pack","effectiveFromWeek":202423,"effectiveToWeek":202534,"addFlag":{"id":1,"state":"100"},"delFlag":{"id":1,"state":"100"}},"productCodeID":23387,"productStatusID":71023,"productName":"Pen Box"}]

问题根源是CROSS APPLY @Output会让每条JSON中的产品数据与所有新增的父表ID做笛卡尔积，导致重复插入。核心要实现的是每条JSON里的父表数据和对应产品数据一一绑定，避免交叉关联。

方案一：用临时表保存JSON完整数据及唯一标识 #

先把所有JSON数据解析到临时表，保留每条记录的索引（来自OPENJSON的[key]字段），插入父表时同步输出该索引和新增的父ID，最后通过索引关联插入产品表，彻底避免笛卡尔积：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CREATE_PRODUCT]
    @parentJSONData NVARCHAR(MAX)
AS
BEGIN
BEGIN TRANSACTION;
    -- 保存解析后的完整JSON数据，含每条记录的索引
    DECLARE @JsonData TABLE (
        JsonIndex INT,
        PARENT_NAME NVARCHAR(50),
        EFFECTIVE_FROM_WEEK INT,
        EFFECTIVE_TO_WEEK INT,
        ADD_FLAG INT,
        DEL_FLAG INT,
        PRODUCT_CODE_ID INT,
        PRODUCT_STATUS_ID INT,
        PRODUCT_NAME NVARCHAR(50)
    )

    -- 保存插入父表后的ID与对应JSON索引的关联
    DECLARE @InsertedParents TABLE (
        JsonIndex INT,
        PARENT_ID INT
    )

    DECLARE @ErrorMessage  nvarchar(MAX)
    DECLARE @ErrorSeverity int
    DECLARE @ErrorState    int

BEGIN TRY
    -- 解析JSON到临时表，保留每条记录的索引
    INSERT INTO @JsonData
    SELECT
        CAST([key] AS INT) AS JsonIndex,
        JSON_VALUE(value, '$.parent.parentName') AS PARENT_NAME,
        JSON_VALUE(value, '$.parent.effectiveFromWeek') AS EFFECTIVE_FROM_WEEK,
        JSON_VALUE(value, '$.parent.effectiveToWeek') AS EFFECTIVE_TO_WEEK,
        JSON_VALUE(value, '$.parent.addFlag.id') AS ADD_FLAG,
        JSON_VALUE(value, '$.parent.delFlag.id') AS DEL_FLAG,
        JSON_VALUE(value, '$.productCodeID') AS PRODUCT_CODE_ID,
        JSON_VALUE(value, '$.productStatusID') AS PRODUCT_STATUS_ID,
        JSON_VALUE(value, '$.productName') AS PRODUCT_NAME
    FROM OPENJSON(@parentJSONData)

    -- 插入父表，同时输出新增ID和对应JSON索引
    INSERT INTO PARENT 
        ([PARENT_NAME], [EFFECTIVE_FROM_WEEK], [EFFECTIVE_TO_WEEK], [ADD_FLAG], [DEL_FLAG])
    OUTPUT jd.JsonIndex, inserted.ID INTO @InsertedParents
    SELECT 
        PARENT_NAME, 
        EFFECTIVE_FROM_WEEK, 
        EFFECTIVE_TO_WEEK, 
        ADD_FLAG, 
        DEL_FLAG
    FROM @JsonData jd

    -- 通过JSON索引关联，插入产品表
    INSERT INTO PRODUCT
        ([PRODUCT_CODE_ID], [PRODUCT_STATUS_ID], [PRODUCT_NAME], [PARENT_ID])
    SELECT 
        jd.PRODUCT_CODE_ID, 
        jd.PRODUCT_STATUS_ID, 
        jd.PRODUCT_NAME, 
        ip.PARENT_ID
    FROM @JsonData jd
    JOIN @InsertedParents ip ON jd.JsonIndex = ip.JsonIndex

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END;
GO

方案二：通过父表唯一字段关联（简化方案） #

如果单次输入的PARENT_NAME保证唯一，可以直接通过父表名称关联JSON中的父数据和产品数据，无需额外保存索引：

注意：此方案依赖PARENT_NAME的唯一性，若存在重复名称会导致关联错误，优先选择方案一。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CREATE_PRODUCT]
    @parentJSONData NVARCHAR(MAX)
AS
BEGIN
BEGIN TRANSACTION;
    DECLARE @InsertedParents TABLE (
        PARENT_ID INT,
        PARENT_NAME NVARCHAR(50)
    )

    DECLARE @ErrorMessage  nvarchar(MAX)
    DECLARE @ErrorSeverity int
    DECLARE @ErrorState    int

BEGIN TRY
    -- 插入父表，输出ID和名称
    INSERT INTO PARENT 
        ([PARENT_NAME], [EFFECTIVE_FROM_WEEK], [EFFECTIVE_TO_WEEK], [ADD_FLAG], [DEL_FLAG])
    OUTPUT inserted.ID, inserted.PARENT_NAME INTO @InsertedParents
    SELECT 
        [PARENT_NAME], 
        [EFFECTIVE_FROM_WEEK], 
        [EFFECTIVE_TO_WEEK], 
        [ADD_FLAG], 
        [DEL_FLAG]
    FROM OPENJSON(@parentJSONData)  
    WITH (
        PARENT_NAME      NVARCHAR(50) '$.parent.parentName'
        , EFFECTIVE_FROM_WEEK INT '$.parent.effectiveFromWeek'
        , EFFECTIVE_TO_WEEK   INT '$.parent.effectiveToWeek'
        , ADD_FLAG        INT '$.parent.addFlag.id'
        , DEL_FLAG        INT '$.parent.delFlag.id'
    ) AS jsonValues;

    -- 通过PARENT_NAME关联，插入产品表
    INSERT INTO PRODUCT
        ([PRODUCT_CODE_ID], [PRODUCT_STATUS_ID], [PRODUCT_NAME], [PARENT_ID])
    SELECT 
        jv.PRODUCT_CODE_ID, 
        jv.PRODUCT_STATUS_ID, 
        jv.PRODUCT_NAME, 
        ip.PARENT_ID
    FROM OPENJSON(@parentJSONData) 
    WITH (
        PARENT_NAME      NVARCHAR(50) '$.parent.parentName'
        , PRODUCT_CODE_ID      INT '$.productCodeID'
        , PRODUCT_STATUS_ID INT '$.productStatusID'
        , PRODUCT_NAME   NVARCHAR(50) '$.productName'
    ) AS jv
    JOIN @InsertedParents ip ON jv.PARENT_NAME = ip.PARENT_NAME

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END;
GO

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