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

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. An outline is also helpful for achieving concision. It keeps the reader from getting distracted by unnecessary words and off-topic discussions. It also helps you write faster and more effectively. Having a clear outline will save you EssayMap on the day of the paper. It will help you get your essay done more efficiently and make it more appealing to the readers.

    Once you have an outline, it is important to group the topics according to their importance. For example, a literary essay may group ideas into themes, while a history essay might group them according to key turning points or trends. In general, an essay's body should be organized around three major themes, with several paragraphs covering each theme. Ensure that each category has a connection to the essay's thesis.

    ReplyDelete
  3. Hello everyone! If you're looking for high-quality banners in San Francisco, I highly recommend checking out Banner Printing San Francisco. They offer a wide variety of customizable banners suitable for indoor or outdoor use, with fast turnaround times and competitive pricing. Whether you need a banner for a business event, trade show, or any other occasion, their team of experts will work with you every step of the way to ensure your banner exceeds your expectations. Follow this link to learn more: https://bannerprintingsanfrancisco.com/

    ReplyDelete
  4. When it comes to technology, staying updated is crucial, and the TSQL tutorial offers valuable insights into database management. Understanding TSQL can enhance one's proficiency in handling data effectively. However, if you're seeking assistance with writing assignments related to technology, it's essential to rely on the bestwritingservice that guarantees high-quality, original content. This ensures that you receive accurate information and maintain academic integrity while exploring the fascinating world of technology and TSQL.

    ReplyDelete