Archive

Posts Tagged ‘BULK INSERT’

How to import CSVs from Azure Blob Storage to SQL Server 2017 ?

October 26, 2017 Leave a comment

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

capture20171024154723249

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

capture20171024155032516

Once data is successfully imported, let us review the data imported

 SELECT productid,
        productname,
        supplierid,
        categoryid,
        unitprice,
        discontinued FROM dbo.tmpProducts

Output

capture20171024155144082

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 !!!

Advertisements
%d bloggers like this: