mercredi 31 octobre 2018

IF EXISTS (check if json object contains unique id), UPDATE table, ELSE INSERT into table

I want to use IF EXISTS in my query to check whether an object contains a "seqId" or not. I'm trying to update or insert into a table from OPENJSON. This json object @identObj has an array of objects, some objects may have a "seqId," and some might not. The ones that have a "seqId" should update into the "case_ident_to_inv" table, if the "seqId" does not exist it should insert into that table, the "case_ident_to_inv" table. The output of inserting into the table is a unique seqId. I tried implementing an IF EXISTS statement and I thought the syntax was correct but I'm getting this error:

Incorrect syntax near 'IF'.

DECLARE @identObj NVARCHAR(MAX) = N'[
  {  
    "investigators": [
         {"importId": 177, "userId": 120, "invTypeCd": "LI", "seqId": 50}, 
         {"importId": 177, "userId": 124, "invTypeCd": "SI", "seqId": 50}, 
         {"importId": 177, "userId": 120, "invTypeCd": "RV", "seqId": 50}, 
         {"importId": 177, "userId": 123, "invTypeCd": "SI"},
     ]
   }
]';

Data for "case_ident_to_inv" table

+------------+--------+-------------+---------------+-------+
|  importId  | userId |  invTypeCd  |  caseIdentId  | seqId |
+------------+--------+-------------+---------------+-------+
| 177        | 120    | LI          | 10200         | 50    | 
| 177        | 124    | SI          | 10200         | 51    | 
| 177        | 120    | RV          | 10200         | 52    |
| 177        | 123    | SI          | 10200         |       |<--- seqId OUTPUT AFTER INSERT
+------------+--------+-------------+---------------+-------+

The caseIdentId and importId will always be the same, there can be identical userId's and invTypeCd's, seqId is always unique.

Stored Procedure

select I.*
INTO #tmpInvs
FROM OPENJSON(@identObj)
WITH (
    invs NVARCHAR(MAX) AS JSON
) AS caseIdentInvs
CROSS APPLY OPENJSON (caseIdentInvs.invs)
WITH (
    userId INT,
    invTypeCd CHAR(5),
    importId INT,
    seqId INT
) I;

WITH cte AS
(
    SELECT i.*,ci.case_ident_id AS case_ident_id, ki.inv_type_name AS inv_type_name
    FROM #tmpInvs i
    INNER JOIN case_idents ci ON i.importId=ci.import_id
    INNER JOIN kdd_inv_type ki ON i.invTypeCd=ki.inv_type_cd
)

IF EXISTS (SELECT i.seqId FROM #tmpInvs i WHERE i.seqId != NULL)
UPDATE T
SET
    inv_id = ct.userId,
    inv_type_cd = ct.invTypeCd,
    inv_type_name = ct.inv_type_name
FROM case_ident_to_inv T
INNER JOIN cte AS ct ON ct.case_ident_id = T.case_ident_id

WHERE seq_id = ct.seqId

ELSE

INSERT INTO case_ident_to_inv(inv_id, case_ident_id, inv_type_cd, inv_type_name)
SELECT userId, case_ident_id, invTypeCd, inv_type_name
FROM cte

Aucun commentaire:

Enregistrer un commentaire