使用 JSON 加载数据时在存储过程中处理 CROSS APPLY

问：

我有 2 个表要使用 SQL Server 中的 SP 进行填充。就我而言，我将传递一个 JSON 数组作为数据输入，然后填充数据。在这 2 个表中，所有主键都是自动生成的。

当我生成表时，我需要将新创建的 s 用作每条记录的表中的外键。我能够使用下面的 SP 执行此操作，但是当我使用它时，它会将记录复制为数组的大小。PARENTIDPRODUCTCROSS 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 的情况下执行此操作，请提出建议。PRODUCT