How to Load JSON File to SQL Server Table by using TSQL in SSMS - TSQL Tutorial

How to Load JSON File to SQL Server Table by using TSQL in SSMS - TSQL Tutorial

In this blog post, we are going to learn how we can load JSON file to SQL Server table by using TSQL.
I have generated below JSON file online so we can use for our demo.



Open SQL Server Management Studio (SSMS) and use the script I have provided below, First of all we have to declare the variable which is "Declare @JSON varchar (max), which I have Highlighted in this picture.

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'c:\MyJsonFile.json', SINGLE_CLOB) import
insert into dbo.SSISJson
SELECT * FROM OPENJSON @JSON)
WITH
(
 [ID] INT,
 [first_name] varchar(100),
 [last_name] varchar(100),
 [email] varchar(100),
 [gender] varchar(20),
 [ip_address] varchar(10)
)



By using OPENROWSET function we have imported the JSON Files in @JSON variable.



OPENJSON is table value function that can convert JSON data into columns and rows. As you can see below I have use OPENJSON and provided our variable @JSON as input so we can get data in tabular format.



 Next step we have to describe the list of columns those we would like to get from our JSON data. If you do not want to read all column data, you can provide the list of only required columns.


If you want to Insert this data in some table just follow the steps:
(SELECT * into dbo.JsonTestTable)




Once you execute the TSQL statement, all the data from JSON will be inserted into table.





Video Demo : How to load Json file to SQL Server Table by using TSQL

2 comments: