How to import JSON files into SQL Server

       SQL Server 2016 introduced support for JSON data. Everyone was expecting to see a new data type for JSON but Microsoft decided to use nvarchar(max) rather than creating a new data type for it for good reasons. JSON data is everywhere on the web, challenge is how are we going to import this data into SQL Server. What is the best way?

       SQL Server introduced OPENROWSET function long time ago. It is a table-valued function that can read data from any file (local or network). You need to be sure that SQL Server has the rights to reach and read the file you are trying import. This function returns a single column table that contains the file content. If you will look at its documentation, you will see that it has a lot of options. I am going to focus on the BULK option in this post.

       BULK option requires you to use an alias in the FROM clause. For this demo, I am going to use this public JSON file. I have downloaded the file and moved it to a folder that can be reach by SQL Server.

        Now, let's load this file by using OPENROWSET function and see what the result is going to look like. As I said before, OPENROWSET has a lot of options, In the following example I am using SINGLE_CLOB option. By adding this option, we are forcing function to load the entire contents of a file as text value. SINGLE_CLOB stands for Single Character large Object.

SELECT *
FROM OPENROWSET (BULK 'C:\sampledata.txt', SINGLE_CLOB) as importData

        Alright, now we have the content in a table, but it's not pretty. Second step, I am going to show you how to parse this file's content into rows and columns. To do that, we can use the OPENJSON function with CROSS APPLY in our query.

SELECT *
FROM OPENROWSET (BULK 'C:\sampledata.txt', SINGLE_CLOB) as importData
CROSS APPLY OPENJSON(BulkColumn)

     Looks better, but I think we can do even better. I want to a create table from the value column, I am going to use the WITH common table expression to return a custom table.

SELECT *
FROM OPENROWSET (BULK 'C:\sampledata.txt', SINGLE_CLOB) as importData
CROSS APPLY OPENJSON(BulkColumn)

WITH (nm varchar(50), 

cy varchar(50), hse varchar(50), yrs varchar(50)) as MyTable

     OPENROWSET function is a great option to import JSON files into SQL Server. Also It is pretty easy to reshape the loaded JSON data by using the JSON functions.