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)
    INSERT INTO dbo.SystemRecord (
    , [RecordedDateTimeLocal]
    , [CpuPctProcessorTime]
    , [MemAvailGbytes])
        FROM OPENJSON(@json)
        WITH (
          RecordedDateTime      DATETIME2(0) '$.dateTime'
        , RecordedDateTimeLocal DATETIME2(0) '$.dateTimeLocal'
        , CpuPctProcessorTime   SMALLINT     '$.cpuPctProcessorTime'
        , MemAvailGbytes        SMALLINT     '$.memAvailGbytes'
        ) AS jsonValues


    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.

    select * from dbo.SystemRecord

Tags: mysql

Mobile Applications

Video Content

Bigdata & NoSQL

SaaS Applications



Alexa Certified Site Stats for