如何在SQL Server中将数组及嵌套数组插入两张不同表

以下是实现需求的完整存储过程代码：

CREATE PROCEDURE InsertCustomersWithPlants
    @customerJson NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 存储插入的客户ID与对应的客户编码，用于关联原JSON记录
    DECLARE @InsertedCustomers TABLE (
        CustomerId INT,
        CustomerCode INT
    );

    -- 第一步：插入客户主表，排除plantIds字段
    INSERT INTO customers (
        customerName,
        contactNo,
        email,
        address,
        region,
        countryId,
        cityId,
        groupId,
        customerCode
    )
    OUTPUT inserted.CustomerId, inserted.CustomerCode INTO @InsertedCustomers
    SELECT
        customerName,
        contactNo,
        email,
        address,
        region,
        countryId,
        cityId,
        groupId,
        customerCode
    FROM OPENJSON(@customerJson)
    WITH (
        customerName NVARCHAR(100),
        contactNo BIGINT,
        email NVARCHAR(100),
        address NVARCHAR(200),
        region NVARCHAR(50),
        countryId INT,
        cityId INT,
        groupId INT,
        customerCode INT,
        plantIds NVARCHAR(MAX) AS JSON -- 标记为JSON数组，后续拆分
    );

    -- 第二步：插入客户-工厂关联表，关联刚生成的CustomerId与对应的PlantId
    INSERT INTO customer_plants (CustomerId, PlantId)
    SELECT
        ic.CustomerId,
        CAST(p.value AS INT) AS PlantId
    FROM @InsertedCustomers ic
    JOIN OPENJSON(@customerJson) cj
        ON ic.CustomerCode = cj.customerCode
    CROSS APPLY OPENJSON(cj.plantIds) p;
END;

关键逻辑说明 #

• 批量插入的ID跟踪：使用OUTPUT子句将所有插入的客户ID和对应编码存入表变量，避免了SCOPE_IDENTITY()只能获取最后一条ID的局限，确保每条客户记录都能关联到对应的工厂ID。
• JSON解析与拆分：通过OPENJSON解析输入的JSON字符串，将plantIds字段标记为JSON类型后，再用CROSS APPLY拆分数组中的每个工厂ID，实现一对多的关联插入。
• 关联匹配：通过customerCode作为唯一标识关联插入后的客户记录与原JSON数据，确保工厂ID能准确对应到所属客户。

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