Bulk load data from JSON file from Blob storage to Azure SQL Database

Bulk load from JSON or CSV files from Azure SQL Blob to Azure SQL Database is a common scenario, I want to show you how you can import data from Azure SQL Blob.

Step1 -Create Master Key

If that’s first time you need to create Master Key, As Microsoft also explains here the database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. See the referral link here.

CREATE MASTER KEY [ ENCRYPTION BY PASSWORD ='password' ]

That’s my Master Key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='StorngPasswordforMydatabase@@' 

If you already have created the master Key, you’ll get the following error message,
and you have to drop the key first or you can skip this step. How delete the Master Key? See this link

There is already a master key in the database. Please drop it before performing this statement.

Step2 -You need to have “SHARED ACCESS SIGNATURE”

I already have created my storage account, then containers, I also uploaded the sample.json file there. Go to the storage account and from the left blade select “Shared access signature” then choose all details like “Allowed service and permission”, You can also say what’s start and expiry date/time.

After you choose all those details click on “Generate SAS and connection string” button and copy the SAS token like below screenshot. Don’t forget to remove “?” character as we do not need that.

Step3 -Create database scoped credential

With SHARED ACCESS SIGNATURE which you created in last step now need to create database scoped credential Please see here the syntax and more details

CREATE DATABASE SCOPED CREDENTIAL MyNewCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2020-08-04&ss=bfqt&srt=co&sp=rwdlacupitfx&se=2022-03-31T12:50:26Z&st=2022-03-31T04:50:26Z&spr=https&sig=5fURHLD6E8Qlv4Uen3uecLbz73F5TeVPgAq1A4eyNDo%3D';

Step4- External Data Source

Now it’s time to create data source, You have the location from your Azure storage, the credential also is same as you have created in last step.

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://mystoragetest2022.blob.core.windows.net',
       CREDENTIAL= MyNewCredential);

Once you’ve created External Data Source you can see the under External Data Source in SSMS, that would be like the following screenshot.

Step4- Bulk data into the table with T-SQL

I already have created a table with one column then try to import all data from Json file into the table. As you can see I have completed successfully.

BULK INSERT [dbo].[TestForImportFromBlob] --I already have created the table
 FROM 'folder/sample.json' --Where is the file in Blob?
     WITH (
      DATA_SOURCE = 'MyDataSource' -- External datasource name created in last steps 

As you see we bulk insert the file from blob storage to Azure SQL Server.

I have created all resources for this demo and I’ll delete all of them after I finish the post.