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'
);
Output
Once data is successfully imported, let us review the data imported
SELECT productid,
productname,
supplierid,
categoryid,
unitprice,
discontinued FROM dbo.tmpProducts
Output
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');
Error Message
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 !!!
SQLXpertise 