How to Import JSON File Into SQL Server Database Using T-SQL (For Beginners)

In this tutorial, we are going to learn step-by-step how to import a #JSON data file into SQL Server database from scratch.
JSON data object is probably one of the most portable data format today, especially you are working with various of API to import or export datasets. In Microsoft SQL Server 2016, the software added few new features allowing users to import JSON data file directly in SQL Server.
📺 How To Build A MS SQL Server SQL Query Tool Using Excel: • Building A MS SQL Serv...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/...
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood...
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
🗓 Get updated on new Python videos → / madeinpython
📘 More tutorial videos on my website → LearnDataAnaly...
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: [e-mail]
#SQLServer #TSQL #JSON

[@sofumzo3219]

Still saving frustrated beginners in 2021! Thank you!

[@shvideo1]

Great video, very well explained. Thank you for putting it together and sharing.

[@onusportugal6771]

Fantastic. Good work. I've been trying for weeks to find a solution and that's what I needed. Thank you very much

[@nikolaybaranov2213]

Thank you, helpful!

[@pradeepnagaraj9277]

Nice Explanation! Thanks!!

[@jakeburns3540]

Very helpful video. Thanks man!

[@damarh]

10:35 CTRL+SHIFT+R to refresh intellisense, since the table was just created.

[@AK-rw8zq]

Awesome work and great explanation.

[@rohitprajapati1642]

the following error occured while doing json to sql Cannot bulk load because the file "‪C:\samplecompany\group.json" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

[@alexadams78]

Thanks Jie, good video! Quick question, if you're refreshing the JSON file with new data, where some of the rows will be duplicate, how do you handle those in the script?

[@darkclouddaj10]

You will have to store the contents of the Jason file in a temp or in memory table and use the Merge command.

[@MrJose1196]

any way to serialize the records you have in a table to json and send it through an api with t-sql?

[@olamideolawole5854]

Thanks for doing this , but I followed the step and I got an error message,that the JSON file is valid but 0 rows affected. any suggestion please?

[@jiejenn]

Can you email me the JSON file that you used to [e-mail]. I suspect there might be syntax error caused the issue.

[@britthale7917]

Great! Just what I needed! Bravo!! I'd like to know limitations, please. (I'm currently working on my first JSON project and learning piece by piece, so feel free to provide links to resources, as you've provided great efforts so far.) What is JSON file size limit? Is the JSON file being stored entirely in memory or is data always read straight from the file? My concern is large files. (i.e. - 200 elements x 1,000,000 recordsets) If working with large datasets, is there a better method possibly?

[@jiejenn]

There's no file size if I remember correctly. When you load a JSON file, the dataset is loaded into system memory first then loaded into SQL Server by batches. If you are dealing with mega size volume dataset and data are coming from JSON dataset, I would probably use other type of database systems like MongoDB instead of SQL Server.

[@HLKatie1]

This is great, thank you!

[@yenping9834]

thanks Jie! one question, how to process when there is nested json? Like { "_id": "5b92437246b39f958ff75771", "age": 21, "gender": "male", "friends": [ { "id": 0, "name": "Joyce Aguirre" }, { "id": 1, "name": "Washington Tillman" } ], }

[@jiejenn]

For nested JSON data, it gets a little bit tricky. First you will have to have all the required tables created. And depending on how many dependent tables, the methods will be different. For example, with 2 or 3 tables nested JSON data, a merge statement should be sufficient, but if you have more than 4 more tables, then it gets a little bit tricky. Usually with nested JSON data set, I will use Python to separate the tables from a JSON data set, then import them to SQL server one by one.

[@jordisperez7961]

THANKS JIE, GREAT HELP! Do you know how can i do the same,but, with a XML?

[@jiejenn]

I might have to do some research and learning on this one as I deal mostly with spreadsheets, text files, and JSON.

[@tuey3891]

Hi Jie! i triewd using this code in a postgresql(pgadmin) database and it didnt work. Does this not work?

[@jiejenn]

PostgresSQL and SQL Server are two completely different database systems therefore the SQL syntax will not be the same as well.

[@srirampattabiraman591]

when i type declare its throwing syntax error can any one help?

[@jiejenn]

When you execute your query what does the error message show? Or were you able to execute your query successful but in SQL Server it is showing syntax error?

[@milothoxha4430]

Dear, i have been following your video but i am having some issues, after executing this is what i get: JSON File is valid Msg 13607, Level 16, State 4, Line 14 JSON path is not properly formatted. Unexpected character 'p' is found at position 1. here is a screenshot of it imgur.com/gaJgk5F On the left side it is the SQL Server, and on the right side it is producs.json, the file that i want to import to sql. A little bit of help would be appriciated. Thank you.

[@milothoxha4430]

Okay, at product_name varchar(50) '$product_name' i have forgotten the dot, '$.product_name', even after this it says 0 rows affected. imgur.com/2gvlFMX please check the above screenshot of my json file.

[@jiejenn]

Most likely there is some formatting issue with your JSON file. Without looking your JSON file data set, it is difficult to help you.

[@milothoxha4430]

Jie Jenn i have posted a screenshot of my JSON dataset on the first comment.

[@user-donpark]

hi. thank your video. if error message say ''JSON FILE is invalid'' i dont know that. please teach me

[@RavenRock88]

Are you awesome or are you awesome? Thank you :-)

[@jiejenn]

Thanks for the compliment. Glad my video helped.

[@andreg62]

Where is the script?

[@jiejenn]

What script?

[@andreg62]

@@jiejenn The script that is in the presentation

[@andreg62]

At 2:04 you said that you would put script in description