How to import JSON file into SQL Server table

Aman Raiyyani
How to import JSON file into SQL Server table

There are several ways to import a JSON file into a SQL Server table, including using built-in functions, using third-party tools, or writing a custom script.

Here is an example of how to import a JSON file into a SQL Server table using the built-in OPENJSON function:

Create a table: Create a table in SQL Server with the same structure as the JSON file.

CREATE TABLE mytable (
    id INT,
    name VARCHAR(50),
    age INT

Import the JSON file: Use the OPENJSON function to import the JSON file into the table.

    id INT '$.id',
    name VARCHAR(50) '$.name',
    age INT '$.age'

The OPENJSON function parses the JSON file and maps the values to the columns in the table. The WITH clause specifies the mapping of the JSON properties to the table columns.

It’s worth noting that the OPENROWSET function requires the BULK option and the SINGLE_CLOB option to be enabled. Also, it may need to enable ad-hoc distributed queries by using the following statement:

Exec sp_configure 'show advanced options', 1; 

Exec sp_configure 'Ad Hoc Distributed Queries', 1; 

It’s important to test the import process in a test environment before moving it to production and to validate the data to make sure that it’s loaded correctly.

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…