Export data from Azure SQL to a blob storage using T-SQL

Question

Hello,

We would like to export the data from an Azure SQL table to the BLOB storage (ideally into Parquet files, but anything else is also acceptable if Synapse is able to read it). We would like to do this in T-SQL so we can lock the table while we are copying the data. We have created an external data source pointing to the BLOB storage and we are able to query the files stored in it with OPENROWSET, but we are not able to insert any data into it.

E.g.

INSERT INTO OPENROWSET(
    BULK 'Query.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'formatquery.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage' )
VALUES(11, 12, 13, 'asdf')

throws the error “Failed to execute query. Error: Incorrect syntax near the keyword 'BULK'.”

Probably the way to do this would be to create an external table, but this seems to be different between SQL Server and Azure SQL, and in Azure SQL it is not possible to write the “table” into simple files.

Is it in any way possible to upload the data to the blob storage using T-SQL only?

Answer 1

You can use External rest endpoint trigger functionality in azure SQL database:

https://devblogs.microsoft.com/azure-sql/azure-sql-database-external-rest-endpoints-integration-public-preview/

below Git provides an example for the same :

https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/azure-storage.ipynb