Tuesday, 21 May 2019

Insert/Update JSON Data into database Table using MS SQL 2017

This is example of Insert Update JSON data into database Table without converting or de-serialize JSON format

1. use string variable in Web API to hold JSON data

--Parameter 
@JsonData nvarchar(max)
IF @JsonData <>'[]'
BEGIN
INSERT INTO YourTable (EmployeeName, City)  
SELECT EmployeeName, City
FROM OPENJSON(@JsonData)
WITH (
EmployeeName nvarchar(100),City nvarchar(100)
)
end


IF @JsonData <>'[]'
BEGIN
UPDATE YourTable 
SET     EmployeeName= J.EmployeeName

FROM   YourTable AS dt
JOIN   OPENJSON(@JsonData )
WITH (
EmployeeName nvarchar(100),    
) J
ON J.EmpId = dt.EmpId

END


No comments:

Post a Comment