I want to update and insert the stock, InvM, and Invoice table with OPENJSON(). I am new at OPENJSON() in SQL Server. I have an array of objects and I want to insert each object to new row of the tables. I want to iterate through every object and insert or update it using Where clause and OPENJSON():

Array of Objects:

DECLARE @f NVARCHAR(MAX) = N'[{
"Batch": "CP008",
"Bonus": -26,
"Code": 002,
"Cost": 50,
"Disc1": 0,
"Name": "Calpax_D Syp 120Ml",
"Price": undefined,
"Quantity": "1",
"SNO": 9,
"STP": 153,
"Stax": 0,
"TP": 50,
"Total": 50,
"invoiceno": 71,
"profit": 156,
"randomnumber": "3MO0FMDLUX0D9P1N7HGV",
"selected": false,
},
{
"Batch": "P009",
"Bonus": 0,
"Code": 823,
"Cost": 237.14999389648438,
"Disc1": 0,
"Name": "PENZOL TAB 40 MG",
"Price": undefined,
"Quantity": "2",
"SNO": 94,
"STP": 263.5,
"Stax": 0,
"TP": 263.5,
"Total": 527,
"invoiceno": 71,
"profit": 156,
"randomnumber": "3MO0FMDLUX0D9P1N7HGV",
"selected": false,
}
]'

How to update the Stock table and reduce the quantity with where condition if the Name of the medicine in the object array matches with the medicine in the Stock table (I came up with this but it is not working correctly):

UPDATE Stock 
SET Qty = Qty - qty  
from OPENJSON(@files) 
with(qty INT '$.Quantity', Name12 VARCHAR(55) '$.Name') 
where Stock.Name = Name12  

Same goes for the InvM and Invoice table I want to insert new row with where condition

insert into InvM (RNDT, Dat, SMID, CID, Total, USR, RefNo, SRID, Txt,InvTime) 
 select RNDT, getdate(), Salesman.SMID, Customer.CID,@total, USR.Name, 0, 
 0,Salesman.Name,CURRENT_TIMESTAMP
 from Salesman, USR,Customer, OPENJSON(@files)  
 with(
    RNDT NVARCHAR(max) '$.randomnumber'
    )
 where USR.Name = 'moiz'



insert into Invoice (SNO, RNDT, Invno, Code, Name, Batch, STP, Qty, Bon, Disc, Stax, NET, 
TP, Cost, Profit)
select SNO, RNDT, InvNo, Code, Name, Batch, STP, Qty, Bon, Disc, Stax, NET, TP, 
Cost,profit  
from OPENJSON(@files) 
with (  
Batch INT '$.Batch',
Bon INT '$.Bouns',
Code INT '$.Code',
Cost INT '$.Cost',
Disc INT '$.Disc1',
Name NVARCHAR(Max) '$.Name',
STP INT '$.STP',
Qty INT '$.Quantity',
SNO INT '$.SNO',
Stax INT '$.Stax',
RNDT NVARCHAR(max) '$.randomnumber',
InvNo INT '$.invoiceno',
TP INT '$.TP',
NET INT '$.Total',
profit INT '$.profit'
)

CodePudding user response：

You need to parse the input JSON with OPENJSON() and update the table using an appropriate JOIN. The following example is a posiible solution to your problem:

Sample data:

SELECT *
INTO Stock
FROM (VALUES
   ('PENZOL TAB 40 MG', 100),
   ('Calpax_D Syp 120Ml', 100)
) v (Name, Quantity)

JSON:

DECLARE @files NVARCHAR(MAX) = N'[
   {
      "Batch":"CP008",
      "Bonus":-26,
      "Code":2,
      "Cost":50,
      "Disc1":0,
      "Name":"Calpax_D Syp 120Ml",
      "Price":"undefined",
      "Quantity":"1",
      "SNO":9,
      "STP":153,
      "Stax":0,
      "TP":50,
      "Total":50,
      "invoiceno":71,
      "profit":156,
      "randomnumber":"3MO0FMDLUX0D9P1N7HGV",
      "selected":false
   },
   {
      "Batch":"P009",
      "Bonus":0,
      "Code":823,
      "Cost":237.14999389648438,
      "Disc1":0,
      "Name":"PENZOL TAB 40 MG",
      "Price":"undefined",
      "Quantity":"2",
      "SNO":94,
      "STP":263.5,
      "Stax":0,
      "TP":263.5,
      "Total":527,
      "invoiceno":71,
      "profit":156,
      "randomnumber":"3MO0FMDLUX0D9P1N7HGV",
      "selected":false
   }
]';

UPDATE statement:

UPDATE s
SET s.Quantity = s.Quantity - j.Quantity
FROM Stock s
JOIN OPENJSON(@files) WITH (
   Name varchar(100) '$.Name', 
   Quantity int '$.Quantity' 
) j ON s.Name = j.Name

Result: