Read the JSON File and insert those records into the table in SQL SERVER

 The JSON file is one of the best options to exchange the data. JSON stands for JavaScript Object Notation.

In this post we see how we will read the JSON file and Parse it and finally, we are loading the data into the database table.

Below is the JSON file.   

This file is having 4 Elements.

Below is the table.

CREATE TABLE Posts_details(                 userID int NULL,                 Id int NULL,                 Post_title varchar(100) NULL,                 Post_body varchar(500) NULL )

Using the OPENROWSET function we are reading the JSON file.

SELECT * FROM OPENROWSET (BULK 'H:\SSIS1\Source\Posts.JSON', SINGLE_CLOB) as import

Getting the below result.   

We are storing this result in the string for parsing.

declare @json varchar(max); set @json = (SELECT * FROM OPENROWSET (BULK 'H:\SSIS1\Source\Posts.JSON', SINGLE_CLOB) as import) print @json

                                         
Using the OPENJSON function we are parsing this data into the rows.

declare @json varchar(max); set @json = (SELECT * FROM OPENROWSET (BULK 'H:\SSIS1\Source\Posts.JSON', SINGLE_CLOB) as import)    SELECT userID ,                                 Id ,                                 title ,                                 body      FROM OPENJSON(@json)           WITH (                  userId NVARCHAR(30) ,                  id NVARCHAR(50) ,                  title NVARCHAR(50),                  body NVARCHAR(50)                );

     

Now we are writing the script to insert the records in the table.

declare @json varchar(max); set @json = (SELECT * FROM OPENROWSET (BULK 'H:\SSIS1\Source\Posts.JSON', SINGLE_CLOB) as import)  insert into Posts_details                  (                                 userID ,                                 Id ,                                 Post_title ,                                 Post_body                   )    SELECT userID ,                                 Id ,                                 title ,                                 body      FROM OPENJSON(@json)           WITH (                  userId NVARCHAR(30) ,                  id NVARCHAR(50) ,                  title NVARCHAR(50),                  body NVARCHAR(50)                );

Before running this script, see the records into the table.     

Now running script  

Record inserted successfully.

See the records in the table.  

Records inserted successfully.