Azure Data Lake - how to insert into external table in AzureSQL DB?

Question

From Azure Data Lake, inserting records to an external table in AzureSQL DB produce the following error:

Error   E_CSC_USER_CANNOTMODIFYEXTERNALTABLE    Modifying external table 'credDB.dbo.BuildInfosClone' is not supported.
Modifying external table 'credDB.dbo.BuildInfosClone' is not supported.
External tables are read-only tables.

How to insert records to external database ? My credential has read-write access. I am using regular Azure SQL DB, not Data Warehouse.

Complete U-SQL code

CREATE DATA SOURCE myDataSource
FROM AZURESQLDB
WITH
(
    PROVIDER_STRING = "Database=MedicusMT2",
    CREDENTIAL = credDB.rnddref_admin,
    REMOTABLE_TYPES = (bool, byte, sbyte, short, ushort, int, uint, long, ulong, decimal, float, double, string, DateTime)
);

CREATE EXTERNAL TABLE IF NOT EXISTS dbo.BuildInfosClone
(
    [Key] string,
    [Value] string
)
FROM myDataSource LOCATION "dbo.BuildInfosClone";

INSERT INTO dbo.BuildInfosClone
( [Key], [Value] )
VALUES
("SampleKey","SampleValue");

wBob wBob · Accepted Answer · 2018-06-13T21:11:56

You cannot currently write directly to Azure SQL Data Warehouse tables using U-SQL. You could either write your data out to flat file then import it using Polybase or use Data Factory to orchestrate the copy.

Alternately you can use Azure Databricks to write directly to SQL Data Warehouse as per this tutorial.

Azure Data Lake - how to insert into external table in AzureSQL DB?

1 Answers