0

I am trying to create a stored procedure that can:

  • Take JSON as input and extract the records in the JSON object to further update or create the record

  • When the record existed in the DB, update the record, if not, create a new record

  • Field Name in JSON is different from the column name in that table so we have to assign them.

Example JSON as input:

{
   "Ticketid":"0001",
   "TicketName":"DAILYTICKET01011",
   "Employee":{
      [
         {
            "employeeid":"1",   // in db, its EmpID
            "employeename":"David", //in db, it EmpName
            "totalpoint":"8"    //in db, it TotalPoint
         },
         {
            "employeeid":"5",   // in db, its EmpID
            "employeename":"Mike", //in db, it EmpName
            "totalpoint":"2"    //in db, it TotalPoint
         }
      ]
   },
   "Task":{
      [
         {
            "taskid":"1",
            "taskname":"Task01",
            "employeeid": 2,
            "size":1
         },
         {
            "taskid":"4",
            "taskname":"Task50",
            "employeeid": 1,
            "size":5
         }
      ]
   }
}

Example database: there are 2 existing tables, Emp and Task, they have one-2-many relationship.

Emp table:

EmpId      EmpName      TotalPoint
----------------------------------
1           David         3.0
2           Marry         3.3
3           Jason         2.4
4           Eric          4.3

Task table:

TaskId   TaskName      EmpId    TaskSize
-----------------------------------------
1         Task01         3        3.0
2         Task23         1        3.3
3         Task08         4        8.0

After executing the stored procedure, it will take that JSON input and our two tables are now become. It is either updating or creating against out tables.

Emp table:

EmpId      EmpName      TotalPoint
-----------------------------------
1           David         8.0
2           Marry         3.3
3           Jason         2.4
4           Eric          4.3
5           Mike          2.0

Task table:

TaskId   TaskName      EmpId    TaskSize
----------------------------------------
1         Task01         2        1.0
2         Task23         1        3.3
3         Task08         4        8.0
4         Task50         1        5.0
Danielisgoood1001
  • 1
  • 1
  • You have a number of steps here: parse the JSON using `OPENJSON`, then upsert that into a table (possibly using `MERGE`. You will need to do this multiple times, once for each table you want to insert into – Charlieface Sep 18 '22 at 02:36

0 Answers0