How to import data from JSON file to SQL Server DB?

In my previous article, we have seen how to export the data from an SQL Server database table to a JSON file. Now we will see how to import data from a JSON file to a table.

For this illustration, I’m using the json file exported using the sample scripts from my previous article.

To start with, let’s see how to use OPENROWSET to import a JSON file to SQL Server. OPENROWSET is a T-SQL relational operator which helps us to read external sources of data. We can also use it to BULK import data from a file and return a single column of data. The column is named BulkColumn. Here we are using OPENROWSET to bulk import from a JSON file using the bulk option Single_CLOB.

SELECT * FROM OPENROWSET 
	(BULK 'C:\Beaulin-Temp\cities.json', Single_CLOB) As import;
GO

Now we will see how to assign the data imported using OPENROWSET to a variable. Modify above SELECT statement and assign the returned column of data called BulkColumn to a variable. Then you can use another select statement along with OPENJSON to display the data stored in the variable in a table format.

Declare @JSON varchar(max)
SELECT @JSON = BulkColumn
	FROM OPENROWSET (BULK 'C:\Beaulin-Temp\cities.json', SINGLE_CLOB) import

SELECT * FROM OPENJSON (@JSON)
WITH  (
   [CityID] int,  
   [CityName] nvarchar(50),  
   [LatestRecordedPopulation] bigint);
GO

Finally we will see how to insert the data stored in the variable to a database table. For this, I have created a table called Cities with the column data type, similar to the data types.

/* Create a Table */
CREATE TABLE [dbo].[Cities](
	[CityID] [int] NOT NULL,
	[CityName] [nvarchar](50) NOT NULL,
	[LatestRecordedPopulation] [bigint] NULL
) ON [PRIMARY]
GO

/* Import json file and insert the data to the table. */
Declare @JSON varchar(max)
SELECT @JSON = BulkColumn
	FROM OPENROWSET (BULK 'C:\Beaulin-Temp\cities.json', SINGLE_CLOB) import
INSERT INTO Cities ([CityID], [CityName], [LatestRecordedPopulation])
SELECT * FROM OPENJSON (@JSON)
WITH  (
   [CityID] int,  
   [CityName] nvarchar(50),  
   [LatestRecordedPopulation] bigint);
GO

/* Check the data stored in the table. */
SELECT * FROM [dbo].[Cities]
GO

NOTE: Using this method, we can import a JSON file where the data is stored in a single line. Data from Line-Delimited JSON file cannot be imported as this method imports only the first line of data.

Reference

• More about OPENROWSET at Microsoft Docs.