1

I have a procedure that should OPENJSON and insert into two tables at the same time, one row for every object within the JSON array. For example these two objects in the array will insert two rows of data into the "case_idents" table and output 2 case_ident_id's (101 & 102) and insert those two id's into the "case_to_case_idents" table. This procedure doesn't do that, instead only case_ident_id (102) gets inserted twice into the 'case_to_case_idents" table creating a duplicate instead of creating unique row objects.

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
Improve this question
2
  • Would case_id always be unique in @identObj? Reading other comments I understand you could make multiple calls and case_id could be the same in two different calls, but would there ever be multiple case_id in the same @identObj value? If you won't have multiples can use OUTPUT on your first insert to capture all the identity values at time of insert and use that in the second insert. Let me know, I can put an example up. Commented Oct 18, 2018 at 20:38
  • case_id would not be unique in @identObj. Since each case may have multiple case idents. Commented Oct 18, 2018 at 22:43

3 Answers 3

Reset to default
2

This kind of thing is best to be solved with an intermediate staging table:

First of all I read your JSON data into a temp table just as-is. At this point you can do any kind of validation and cleansing before you enter the volume into your target tables.

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"
       }
    ]';

SELECT B.* 
INTO #tmpCases
FROM OPENJSON(@identObj) A
CROSS APPLY OPENJSON(A.value) 
WITH
(
    [name]       VARCHAR(100)
   ,entityTypeCd VARCHAR(100)
   ,identStatus  VARCHAR(100)
   ,caseId       INT
   ,caseStatusCd VARCHAR(100)
) B;

--A mockup of your target tables

CREATE TABLE #case_idents(ID INT IDENTITY, [name] VARCHAR(100),entity_type_cd VARCHAR(100),ident_status VARCHAR(100),case_id INT);
CREATE TABLE #case_to_case_ident(case_id INT, case_ident_id INT /*fk to other table*/,case_status_cd VARCHAR(100),case_ident_status_cd VARCHAR(100));

--insert the first one

INSERT INTO #case_idents([name],entity_type_cd,ident_status,case_id)
SELECT [name],entityTypeCd,identStatus,caseId 
FROM #tmpCases;

--insert the second one

WITH cte AS
(
    SELECT c.*,ci.ID AS case_ident_id
    FROM #tmpCases c
    INNER JOIN #case_idents ci ON c.caseId=ci.case_id
)
INSERT INTO #case_to_case_ident(case_id,case_ident_id,case_status_cd,case_ident_status_cd)
SELECT caseId,case_ident_id,caseStatusCd,identStatus
FROM cte;

--check the result

SELECT * FROM #case_idents
SELECT * FROM #case_to_case_ident;
GO

--clean (careful with real data!)

DROP TABLE #tmpCases;
DROP TABLE #case_idents;
DROP TABLE #case_to_case_ident;
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

What does the .* mean when SELECT B.*? Also I have tables that I created not through a procedure and that is where I want the permanent data to be stored, so I'm wondering why I would need to drop the tables?
@hypnagogia B. * resolves the With-clause. And no, don't delete your tables. That's why I wrote "mockup". In my scenario I delete them for test purposes. Hope this is clear now....
The procedure worked in adding the correct data to both tables but I have red squiggles that say "invalid column name" for a few of the columns when selecting from the #tmpCases table. I'm assuming it's because the table #tmpCases isn't created yet and IntelliSense isn't recognizing the object until I run the procedure.
@hypnagogia True, intellisense is not smart enough to reckon the dynamically created columns from the With-clause of openjson...
Actually, upon more testing adding data to the tables it looks like the data is being inserted multiple times into the second table "case_to_case_idents." For example, if I execute the procedure one time I get two case_ident_id's inserted into the "case_idents" table, and the same amount gets added to the "case_to_case_ident" table the first time. But when I run the procedure again I get two more unique case_ident_id's into the "case_idents" table but the "case_to_case_ident" table gets the previous two id's appended to it as well as the new ones. For example, (130, 131, 130, 131, 132, 133).
|
2

The way you are using SCOPE_IDENTITY() is why on your second insert both records have the same value.

SCOPE_IDENTITY() - Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

SCOPE_IDENTITY() will only return last identity value.

If you have a unique identifier in your @identObj data you can use an OUTPUT clause and capture all the identity column values into at table variable at time of insert for your data and then join on that in your second insert.

I asked the question, but I'll assume so you can explore this option as well, that case_id would be unique in @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];
Improve this answer

1 Comment

Thank you, I responded to your comment to my question. The case id's are not unique. Also, I didn't know that about SCOPE_IDENTITY(). I was baffled by why it only added the last ID in the array.
0

Thank you @Shnugo and @Tim, putting this into a temporary table worked, but I had to change the "insert into second table" part of the procedure because rows were being duplicated and the ident objects can have the same caseId, so there was that issue of joining the case_ident table to the #tmpCase table by the use of the caseId, which is not unique to the case_to_case_ident table. Adding the same importId column to both the case_ident table and #tmpCases table solved the issue of caseId not being unique, as well as not duplicating the rows in the case_to_case_ident table.

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

SELECT B.* 
INTO #tmpCases
FROM OPENJSON(@identObj) A
CROSS APPLY OPENJSON(A.value) 
WITH
(
   [name] NVARCHAR(50),
   entityTypeCd CHAR(5),
   identStatus CHAR(5),
   caseId INT,
   caseStatusCd CHAR(5),
   importId INT
) B;

INSERT INTO case_idents 
(
    [name],
    entity_type_cd, 
    ident_status, 
    case_id, 
    import_id
)
SELECT 
    [name], 
    entityTypeCd, 
    identStatus, 
    caseId, 
    importId 
FROM #tmpCases;

-- Insert into second table

MERGE case_to_case_ident AS cc
USING (
    case_idents ci 
    INNER JOIN #tmpCases t 
    ON ci.import_id = t.importId
)
ON ci.case_ident_id = cc.case_ident_id
WHEN NOT MATCHED THEN
INSERT 
(
    case_id,
    case_ident_id,
    case_status_cd,
    case_ident_status_cd
)
VALUES
(
    ci.case_id, 
    ci.case_ident_id, 
    t.caseStatusCd, 
    ci.ident_status
);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.