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
Select statement with OPENROWSET

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
Import Data From JSON File To SQL Server

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


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.