Skip to content
Related Articles
Get the best out of our app
GeeksforGeeks App
Open App

Related Articles

How to Import JSON Data into SQL Server?

Improve Article
Save Article
Like Article
Improve Article
Save Article
Like Article

JSON refers to Javascript Object Notation. It is a popular text data format used to exchange data on modern web and mobile applications. It is based on a subset of the Javascript programming language. It is used to store random data in log files or NoSQL.

It also enables us to integrate NoSQL and related concepts into the same database. 

Advantage of  JSON data:

  • Lightweight data-interchange format.
  • Easy for humans to read and write.
  • Easy for machines to parse and generate.

 JSON data using built-in functions and operators:

  • We can parse the JSON text and read or modify the values.
  • Transform JSON objects into table format.
  • We can run any Transact-SQL query on the converted JSON objects.
  • Transform the results of Transact-SQL queries back to JSON format.
ISJSON It tests whether a string contains JSON data or not
JSON_VALUE It extracts a scalar value from a JSON string.
JSON_QUERY It extracts an object or an array from a JSON string.
JSON_MODIFY It changes a value in a JSON string.


Now, to import JSON data in the SQL server, we will use OPENROWSET (BULK). 

Step 1: Use of OPENROWSET(BULK) 

It is a table-valued function that can read data from any file.. It returns a table with a single column that contains all the contents of the file. It can just load the entire contents of a file as a text value. (This single large value is known as a single character large object or SINGLE_CLOB.)


SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name;

It reads the content of the file and returns it in BulkColumn. The correlation name must be specified. We have the JSON file named “file1.json”. Content of JSON file:


[{"Firstname": "ROMY", "Lastname": "KUMARI", "Gender": "female", "AGE" : 22 },
{"Firstname": "PUSHKAR", "Lastname": "JHA", "Gender": "male", "AGE" : 22 },
{"Firstname": "SHALINI", "Lastname": "JHA", "Gender": "female", "AGE" : 21 },
{"Firstname": "SAMBHAVI", "Lastname": "JHA", "Gender": "female", "AGE" : 18 } ]

Step 2: Import file1.json into SQL server


SELECT * FROM OPENROWSET (BULK 'E:\file1.json', Single_CLOB) AS import;


The entire content is returned as a single column.

Step 3: Convert JSON data

  • Declare a variable.
  • Store data of Bulkcolumn in a variable.
  • Use OPENJSON() function for converting the JSON output from a variable into a tabular format.
  • WITH clause along with the column definition (key should be used as column name).


Declare @JSON varchar(max)
FROM OPENROWSET (BULK 'E:\file1.json', SINGLE_CLOB) import
   [Firstname] varchar(20),  
   [Lastname] varchar(20),  
   [Gender] varchar(20),  
   [AGE] int );


My Personal Notes arrow_drop_up
Last Updated : 23 Sep, 2021
Like Article
Save Article
Similar Reads
Related Tutorials