Bulk insert from Azure Storage Account

In some scenarios it is useful to import data into SQL Azure / SQL Server directly from an Azure Storage Account. BULK INSERT can get data directly from Blob Storage¹.

If required, create a master encryption key²:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>';
SELECT * FROM sys.symmetric_keys;

Create a database scoped credential for accessing the storage account. The SAS_TOKEN requires at lest Read and List permissions:

CREATE DATABASE SCOPED CREDENTIAL MyStorageTestSAS 
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '...SAS_TOKEN...';
SELECT * FROM sys.database_scoped_credentials;

Declare an external datasource, pointing it to your storage account, using the credential created in the previous step (Important: no ‘/’ at the end of the location URL):

CREATE EXTERNAL DATA SOURCE MyTestStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://xxxxxxxxxxxxxxxxx.blob.core.windows.net',
    CREDENTIAL = MyStorageTestSAS
);

Now, you can bulk-import the data:

BULK INSERT [Customers] FROM 'mycontainer/abc/customers.csv' WITH (DATA_SOURCE='MyTestStorage', TABLOCK, FORMAT='CSV', FIRSTROW=2, FIELDTERMINATOR =',');
BULK INSERT [Products]  FROM 'mycontainer/abc/products.csv'  WITH (DATA_SOURCE='MyTestStorage', TABLOCK, FORMAT='CSV', FIRSTROW=2, FIELDTERMINATOR =',');
BULK INSERT [Sales]     FROM 'mycontainer/abc/sales.csv'     WITH (DATA_SOURCE='MyTestStorage', TABLOCK, FORMAT='CSV', FIRSTROW=2, FIELDTERMINATOR =',');
...