Infer Schema from .csv file appear in azure blob and generaate DDL script automatically using Azure SQL

user_9261757 0
Viewed 142

Every time the .csv file appearing in the blob storage, i have to create DDL from that manually on azure sql. The data type is based on the value specified for that field.

The file have 400 column, and manually it is taking lots of time.

May someone please suggest how to automate this using SP or script, so when we execute the script, it will create TABLE or DDL script, based on the file in the blob storage.

I am not sure if it is possible, or is there any better way to handle such scenario.

enter image description here

Appreciate yours valuable suggestion.

Many Thanks

azure-blob-storage azure-sql-database tsql
1 Answers

This can be achieved in multiple ways. As you mentioned about automating it, you can use Azure function as well.

Firstly create a function that reads the csv file from blob storage:

Read a CSV Blob file in Azure

Then add the code to generate the DDL statement:

Uploading and Importing CSV File to SQL Server

Azure function can be scheduled or run when new files are added to blob storage.

If this is once a day kind of requirement and can manually be done as well, we can download the file from blob and use the 'Import Flat File' functionality available within SSMS where we can just specify the csv file and it creates the schema based on existing column values.

user_137061310
Related