External table in Blob Storage in Azure SQL(Not Azure SQL DW)

Question

I want to create an external table in blob storage which needs to be managed by Azure SQL. Herewith I have attached my script which is throwing error.

My script:

CREATE DATABASE SCOPED CREDENTIAL some_cred WITH IDENTITY = user1,   

    SECRET = '<Key of Blob Storage container>';  

CREATE EXTERNAL DATA SOURCE TEST

WITH

(

    TYPE=BLOB_STORAGE,

    LOCATION='wasbs://<containername>@accountname.blob.core.windows.net',

    CREDENTIAL= <somecred>`enter code here`

);

CREATE EXTERNAL TABLE dbo.test

(

val VARCHAR(255)

)

WITH

(

DATA_SOURCE = TEST

)

I am getting this error:

External tables are not supported with the provided data source type.

Can anyone help me with this?

Answer 1

Basically, Azure SQL database can only load files in Blob storage via BULK INSERT and OPENROWSET language features.

BULK INSERT dbo.test

FROM 'data/yourFile.txt'

WITH ( DATA_SOURCE = 'YourAzureBlobStorageAccount');

The way you have scripted is only available in SQL server 2016 and Azure SQL Data Warehouse now.

I hope this will help.

Want to become an Azure expert? join Azure Training now!!