How to Insert JSON Data into SQL Server Database

Posted By : Rahul Singh | 21-Sep-2018

mysql

In this blog, I discuss how we insert data as a JSON into SQL Server using a stored procedure. Now, a day we do not create lots of Java's object and another language' object because of memory consumption. So we are using a simple approach to pass data using JSON because JSON object is only one thing that can use anywhere and any platform.

Have you at any point made some REST API that acknowledges JSON and you needed to import this JSON into the database? Possibly you have REST APIs that gets JSON from JQuery, AngularJS, or ReactJS applications? Did you ever call some REST APIs that profits reaction as JSON or stacked some JSON from the record and afterwards you needed to store results in SQL tables? Possibly you needed to stack some JSON archives from Twitter or MongoDB into a database?

Previously, you presumably needed to parse this JSON utilizing JSON.Net or some other serializer or utilize structures to outline into items and after that store them into database utilizing ADO.NET or Entity Framework. With SQL Server 2016, you have another option - simply send the whole JSON content to the database and parse it utilizing new OPENJSON work.

Here we first create one database and then create one table into it.

use tempdb 

CREATE TABLE dbo.SystemRecord(
    RecordedDateTime        datetime2(0) NOT NULL,
    RecordedDateTimeLocal   datetime2(0) NOT NULL,
    CpuPctProcessorTime     smallint     NOT NULL,
    MemAvailGbytes          smallint     NOT NULL
)

Then we make one Stored Procedure with one string parameter and write our code to insert data into a table

CREATE PROCEDURE dbo.InsertSystemRecordData
 
@json NVARCHAR(max)

AS
BEGIN
 
INSERT INTO dbo.SystemRecord (
  [RecordedDateTime]
, [RecordedDateTimeLocal]
, [CpuPctProcessorTime]
, [MemAvailGbytes])
 
    SELECT
        RecordedDateTime
       ,RecordedDateTimeLocal
       ,CpuPctProcessorTime
       ,MemAvailGbytes
    FROM OPENJSON(@json)
    WITH (
      RecordedDateTime      DATETIME2(0) '$.dateTime'
    , RecordedDateTimeLocal DATETIME2(0) '$.dateTimeLocal'
    , CpuPctProcessorTime   SMALLINT     '$.cpuPctProcessorTime'
    , MemAvailGbytes        SMALLINT     '$.memAvailGbytes'
    ) AS jsonValues
 
END

After that, we execute the stored procedure by passing JSON as an INPUT parameter.

EXEC dbo.InsertSystemRecordData @json ='{"dateTime":"2018-03-19T15:15:40.222Z","dateTimeLocal":"2018-03-19T11:15:40.222Z","cpuPctProcessorTime":"0","memAvailGbytes":"28"}'

We can also check inserted data insert successfully or not with this query.