How to save the data of JSON file in local path to a SQL table?

Question

I want to save the data of JSON file which is in my local path to a SQL table.

My Local Path where the JSON file is present is E:\20180824LocalDump.json

I have used the below code in SQL to get the data from JSON file but I got errors.

DECLARE @Details VARCHAR(MAX)  
SELECT @Details = BulkColumn FROM OPENROWSET(BULK 'E:\20180824LocalDump.json', SINGLE_BLOB) JSON;   
SELECT * FROM OPENJSON(@Details)   
WITH(Departure_airport nvarchar(50)  ,
    DisplayName nvarchar(40), 
    Email nvarchar(60),
    Keep_me_deals nvarchar(40),
    Phone_Code nvarchar(50),
    Provider nvarchar(50),
    SignUpDate nvarchar(50),
    Telephone nvarchar(50),
    [Platform] nvarchar(50),
    AppVersion nvarchar(40))

Created a table as shown below:

Create Table Dump
(
Departure_airport nvarchar(50),
DisplayName nvarchar(40),
Email nvarchar(60),
Keep_me_deals nvarchar(40),
Phone_Code nvarchar(50),
Provider nvarchar(50),
SignUpDate nvarchar(50),
Telephone nvarchar(50),
[Platform] nvarchar(50),
AppVersion nvarchar(40)
)

My Json file contents:

[{"Departure_airport":"Test","DisplayName":"Test","Email":"Tst","Keep_me_deals":"Test","Phone_Code":"Test","Provider":"Test","SignUpDate":"Test","Telephone":"Test","Platform":"Test","AppVersion":"Test"},{"Departure_airport":"Test","DisplayName":"Test","Email":"Tst","Keep_me_deals":"Test","Phone_Code":"Test","Provider":"Test","SignUpDate":"Test","Telephone":"Test","Platform":"Test","AppVersion":"Test"},{"Departure_airport":"Test for IE","DisplayName":"Test for IE","Email":"Tst for IE","Keep_me_deals":"Test for IE","Phone_Code":"Test for IE","Provider":"Test for IE","SignUpDate":"Test for IE","Telephone":"Test for IE","Platform":"Test for IE","AppVersion":"Test for IE"},{"Departure_airport":"Test for UK","DisplayName":"Test for UK","Email":"Tst for UK","Keep_me_deals":"Test for UK","Phone_Code":"Test for UK","Provider":"Test for UK","SignUpDate":"Test for UK","Telephone":"Test for UK","Platform":"Test for UK","AppVersion":"Test for UK"},{"Departure_airport":"Test for UK 1","DisplayName":"Test for UK 1","Email":"Tst for UK 1","Keep_me_deals":"Test for UK 1","Phone_Code":"Test for UK 1","Provider":"Test for UK 1","SignUpDate":"Test for UK 1","Telephone":"Test for UK 1","Platform":"Test for UK 1","AppVersion":"Test for UK 1"}]

I am getting an error like:

Invalid object name 'OPENJSON' in SQL.

Please help me on this

Which version of SQL Server are you using? OPENJSON only works starting from version 2016 — Andrea
– Andrea, Commented Aug 24, 2018 at 13:24

BoCoKeith · Accepted Answer · 2018-08-24 14:17:50Z

1

If you are using SQL 2016 or later, you should also check the compatibility level of the database you are running the query from.

USE <your database>;  
GO  
SELECT compatibility_level FROM sys.databases WHERE name = '<your database>';  
GO

If it is not at least 130 you need to (with the kind permission of your DBA) change it.

ALTER DATABASE <your database> SET COMPATIBILITY_LEVEL = 130;  
GO

You probably want to Google for other effects of changing the compatibility level, but it's never caused me a problem.

answered Aug 24, 2018 at 14:17

BoCoKeith

1,0142 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrea · Accepted Answer · 2018-08-27 08:54:05Z

0

Unfortunately you can't use openjson with SQL Server 2014: native json function have been introduced with SQL Server 2016 (more info on openjson here).

An excerpt from the linked page:

APPLIES TO: Server (starting with 2016)

You can still import the json file with OPENROWSET, but you will have to rely on string manipulation functions or on custom functions to extract data from you json file.

answered Aug 27, 2018 at 8:54

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to save the data of JSON file in local path to a SQL table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related