I have my json file in Azure Blog Storage i am trying it to import the data from json file to Azure sql database.I am trying to create sql script to import the data

Question

The script i have used to import the data is

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SAS Token';
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'Location',
CREDENTIAL= MyAzureBlobStorageCredential);

SELECT *
FROM OPENROWSET(BULK 'generated.json', DATA_SOURCE = 'MyAzureBlobStorage',
FORMATFILE='product.fmt', FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as data

facing the error like

![118230-image.png]

I knew i was doing something wrong in the query but i don't get it .can someone please help me on this.
And also i am confused about how to write the format file for the table. Any leads will be appreciated

MY format table script

12.0
4
1 SQLCHAR 0 50 '","' 1 Name SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 '","' 2 Gender SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 '","' 3 Company SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "\t" 4 Email SQL_Latin1_General_CP1_CI_AS

Thanks in advance..!!

Answer 1

Hi @Pulipati Vedanth ,

Thanks for using Microsoft Q&A !!
If you do not have any specific needs to create a FMT file then you can use OPENROWSET with SINGLE_CLOB to import JSON data from a Blob Storage using a query like below -

SELECT id,[name],author FROM OPENROWSET(  
   BULK  'input/books.json',  
   DATA_SOURCE = 'storageadftutorial',  
    SINGLE_CLOB  
    ) AS DataFile  
CROSS APPLY OPENJSON(BulkColumn)  
WITH (id NCHAR(1000),  
[name] NCHAR(500),  
author NCHAR(500)) as book  

Please note here that the data from JSON file is returned as BulkColumn which you can join with OPENJSON which returns each JSON array object as a single row, which you can insert into the required table.

books.json sample content

[
{
"id" : "978-0641723445",
"cat" : ["book","hardcover"],
"name" : "The Lightning Thief",
"author" : "Rick Riordan",
"series_t" : "Percy Jackson and the Olympians",
"sequence_i" : 1,
"genre_s" : "fantasy",
"inStock" : true,
"price" : 12.50,
"pages_i" : 384
}
,
{
"id" : "978-1423103349",
"cat" : ["book","paperback"],
"name" : "The Sea of Monsters",
"author" : "Rick Riordan",
"series_t" : "Percy Jackson and the Olympians",
"sequence_i" : 2,
"genre_s" : "fantasy",
"inStock" : true,
"price" : 6.49,
"pages_i" : 304
}
]

SQL Table:
Create table books( Id NCHAR(1000), [Name] NCHAR(500), Author NCHAR(500) )

Use below to insert into books table

INSERT INTO books with (TABLOCK) (id,[name],author)  
SELECT id,[name],author FROM OPENROWSET(  
   BULK  'input/books.json',  
   DATA_SOURCE = 'storageadftutorial',  
    SINGLE_CLOB  
    ) AS DataFile  
CROSS APPLY OPENJSON(BulkColumn)  
WITH (id NCHAR(1000),  
[name] NCHAR(500),  
author NCHAR(500)) as book  

Please refer to Import JSON documents into SQL Server for details.

Hope this helps.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.