SQL Server 2017 introduces option to import CSV files stored in Azure Blog Storage
In this article we will see how to implement that step by step
Step 1: Create Database Master Encryption Key
This step is optional, if you already have created your database master encryption key, Please skip this
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PleaseUseStrongPasswordHere'
Step 2: Create Database Scoped Credential
CREATE DATABASE SCOPED CREDENTIAL DemoAzureCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'INSERT YOUR SHARED ACCESS SIGNATURE HERE';
Please note when you are copying the Share Access Signature from Azure Portal, it will copy with ? as prefix, and you will need to remove it
Step 3: Create External Data Source
Please make sure to replace sqlxpertise with your own Blob account name and democsvs with your container name
CREATE EXTERNAL DATA SOURCE DemoAzureBlobCSVs WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://sqlxpertise.blob.core.windows.net/democsvs', CREDENTIAL = DemoAzureCredential );
Step 4: Test External Data source connection using OPENROWSET command
SELECT * FROM OPENROWSET( BULK 'DemoProducts.txt', DATA_SOURCE = 'DemoAzureBlobCSVs', SINGLE_CLOB) AS DataFile;
Sample output – This is based on the input file I used and it will vary based on your input file uploaded to Azure Blog Storage
Step 5: Import Data to table
CREATE TABLE tmpProducts ( [productid] [int] IDENTITY(1,1) NOT NULL, [productname] [nvarchar](40) NOT NULL, [supplierid] [int] NOT NULL, [categoryid] [int] NOT NULL, [unitprice] [money] NOT NULL, [discontinued] [bit] NOT NULL, ) BULK INSERT tmpProducts FROM 'DemoProducts.txt' WITH ( DATA_SOURCE = 'DemoAzureBlobCSVs', FORMAT = 'CSV' );
Once data is successfully imported, let us review the data imported
SELECT productid, productname, supplierid, categoryid, unitprice, discontinued FROM dbo.tmpProducts
Note: BULK INSERT is working only for permanent tables and not temporary tables, You will get below error message if you try importing temporary tables
CREATE TABLE #tmpProducts ( [productid] [int] IDENTITY(1,1) NOT NULL, [productname] [nvarchar](40) NOT NULL, [supplierid] [int] NOT NULL, [categoryid] [int] NOT NULL, [unitprice] [money] NOT NULL, [discontinued] [bit] NOT NULL, ) BULK INSERT #tmpProducts FROM 'DemoProducts.txt' WITH (DATA_SOURCE = 'DemoAzureBlobCSVs', FORMAT = 'CSV');
Msg 12703, Level 16, State 1, Line 57
Referenced external data source “DemoAzureBlobCSVs” not found.
Yes, the message is misleading, but I am sure, Microsoft SQL Server team will realize and change this.
Hope you find this post helpful !!!