时间戳

首页 > 解决方案 > 使用 SQL Server 将 JSON 数组对象插入到两个表中

json - 使用 SQL Server 将 JSON 数组对象插入到两个表中

问题描述

我有一个应该 OPENJSON 并同时插入到两个表中的过程，JSON 数组中的每个对象都有一行。例如，数组中的这两个对象会将两行数据插入“case_idents”表并输出 2 个 case_ident_id（101 和 102）并将这两个 id 插入“case_to_case_idents”表。此过程不这样做，而是仅将 case_ident_id (102) 两次插入到“case_to_case_idents”表中，创建副本而不是创建唯一的行对象。

CREATE PROCEDURE case_idents_addAll

@case_ident_id INT OUTPUT,
@seq_id INT OUTPUT,
@identObj NVARCHAR(MAX),

/*
DECLARE @case_ident_id INT
DECLARE @seq_id INT
DECLARE @identObj NVARCHAR(MAX) = N'[
      {  
            "name": "Jack", 
            "entityTypeCd": "SM", 
            "identStatus": "PR", 
            "caseId": 10034,
            "caseStatusCd": "NUA"
       },
       {  
            "name": "Jill", 
            "entityTypeCd": "SF", 
            "identStatus": "PR", 
            "caseId": 10035,
            "caseStatusCd": "NA"
       }
    ]';
 EXECUTE case_idents_addAll @identObj=@identObj, @case_ident_id=@case_ident_id OUTPUT, @seq_id=@seq_id OUTPUT
*/

AS

BEGIN

    INSERT INTO case_idents
    (name, entity_type_cd, ident_status, case_id)
    SELECT name, entity_type_cd, ident_status, case_id
    FROM OPENJSON(@identObj)
    WITH (
        name NVARCHAR(50) '$.name', 
        entity_type_cd CHAR(5) '$.entityTypeCd', 
        ident_status CHAR(5) '$.identStatus', 
        case_id INT '$.caseId'
    )
    SET @case_ident_id=SCOPE_IDENTITY();

    INSERT INTO case_to_case_ident
    (case_id, case_ident_id, case_status_cd, case_ident_status_cd)
    SELECT case_id, @case_ident_id, case_status_cd, case_ident_status_cd
    FROM OPENJSON(@identObj)
    WITH ( 
        case_id INT '$.caseId',
        case_status_cd CHAR(5) '$.caseStatusCd',
        case_ident_status_cd CHAR(5) '$.identStatus'
    )
    SET @seq_id = SCOPE_IDENTITY();

END

标签： jsonsql-servertsql

解决方案

您使用SCOPE_IDENTITY()的方式是为什么在第二次插入时两条记录具有相同的值。

SCOPE_IDENTITY() - 返回插入到同一范围内的标识列中的最后一个标识值。范围是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句在同一个存储过程、函数或批处理中，它们就在同一个范围内。

SCOPE_IDENTITY() 只会返回最后一个身份值。

如果您的 @identObj 数据中有唯一标识符，您可以使用OUTPUT 子句并在插入数据时将所有标识列值捕获到表变量中，然后在第二次插入中加入。

我问了这个问题，但我假设你也可以探索这个选项，case_id 在@identObj 中是唯一的。

--temp tables
CREATE TABLE [#case_idents]
    (
        [ID] INT IDENTITY
      , [name] VARCHAR(255)
      , [entity_type_cd] VARCHAR(255)
      , [ident_status] VARCHAR(255)
      , [case_id] INT
    );
CREATE TABLE [#case_to_case_ident]
    (
        [case_id] INT
      , [case_ident_id] INT
      , [case_status_cd] VARCHAR(255)
      , [case_ident_status_cd] VARCHAR(255)
    );

--We'll use a table variable to capture all the identity field values at time of insert.
DECLARE @OutPut TABLE
    (
        [ID] INT
      , [case_id] INT
    );

DECLARE @identObj NVARCHAR(MAX) = N'[
      {  
            "name": "Jack", 
            "entityTypeCd": "SM", 
            "identStatus": "PR", 
            "caseId": 10034,
            "caseStatusCd": "NUA"
       },
       {  
            "name": "Jill", 
            "entityTypeCd": "SF", 
            "identStatus": "PR", 
            "caseId": 10035,
            "caseStatusCd": "NA"
       }
    ]';

INSERT INTO [#case_idents] (
                               [name]
                             , [entity_type_cd]
                             , [ident_status]
                             , [case_id]
                           )
--capture all the identities at time of insert into our table variable along with what the case_id was.
--Add OUTPUT right after your insert into the table variable.  Will capture the inserted values, the identity values you're after and what case_id they are associated with
OUTPUT [Inserted].[ID]
     , [Inserted].[case_id]
INTO @OutPut
            SELECT [name]
                 , [entity_type_cd]
                 , [ident_status]
                 , [case_id]
            FROM
                   OPENJSON(@identObj)
                       WITH (
                                [name] NVARCHAR(50) '$.name'
                              , [entity_type_cd] CHAR(5) '$.entityTypeCd'
                              , [ident_status] CHAR(5) '$.identStatus'
                              , [case_id] INT '$.caseId'
                            );


INSERT INTO [#case_to_case_ident] (
                                      [case_id]
                                    , [case_ident_id]
                                    , [case_status_cd]
                                    , [case_ident_status_cd]
                                  )
            SELECT     [ident].[case_id]
                     , [op].[ID]
                     , [ident].[case_status_cd]
                     , [ident].[case_ident_status_cd]
            FROM
                       OPENJSON(@identObj)
                           WITH (
                                    [case_id] INT '$.caseId'
                                  , [case_status_cd] CHAR(5) '$.caseStatusCd'
                                  , [case_ident_status_cd] CHAR(5) '$.identStatus'
                                ) AS [ident]
            INNER JOIN @OutPut [op]
                ON [op].[case_id] = [ident].[case_id]; --join here on case_id to get the identity value that was just created before that we captured and stored in our table variable.

SELECT *
FROM   [#case_idents];
SELECT *
FROM   [#case_to_case_ident];

推荐阅读