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.
- More about OPENROWSET at Microsoft Docs.
1 thought on “How to import data from JSON file to SQL Server DB?”
But what, if the filepath comes from outside the script. I tried to declare a @FileName but got an error, that sql expects an string at that point?