Import CSV into SQL Azure Database using SSMS

Question

I have a set of large CSV files with many columns each that I need to import into a SQL Azure database. Ordinarily I would use the import wizard in SQL Server Management Studio. However, the wizard does not appear to be an option when connecting to SQL Azure in SSMS. Is that correct? And if so, what is the recommended tool for accomplishing this task? I'm looking for a tool that will infer from the data what the columns should be allowing me to override the data type as needed. Since I have a lot of columns in each of the files I'd like to avoid the tedious work of manually writing the SQL code to generate the tables.

Kissel

Import into a local database, have SSMS generate the create table script, run that on the Azure db, and then do an INSERT? – Mechanician

@Mechanician I am running to this same issue. How do you run this table script on Azure db to do an INSERT – Atwood

@Mechanician can it be done without an extra DB? – Florance

That's correct you can't connect to SQL Azure and use the import/export wizard. However I notice that it appears to support the new "Import Flat File" feature on the right click menu. Have a crack at that. There are any number of integration methods but the optimal approach depends on your requirements. Data volume? Different columns? Repeatability? – Nyssa

Answer 1

This worked for me:

• Open SQL Server Management Studio
• Connect to Azure
• Right-click the database
• Go to Tasks > Import Data
• Select your flat file(s)
• Upload to Azure SQL and create an SSIS package based on this workflow

I sometimes get errors with CSV files this way, but either using an Excel file or inspecting the options of the CSV data columns in the Import Wizard should suffice.

Make sure you have appropriate permissions assigned to your user account.

They could've / should've made this easier, like a SFTP + insert or a GUI import directly to Azure SQL like in Hue.

Florance

there is no Import Data option when you use Azure tables. You are probably talking about sql server instance on VMs – Grosswardein

Answer 2

When you are transferring any data to SQL Database, the data should be structured. The proces will be to convert your CSV to a table structure and then migrate it directly to SQL Azure. Actually you can write a stored procedure in SSMS to do it all in one.

Because CSV file could be tab, comma, or any other character delimited, you can do bulk insert in local DB first as described here and then sync the table to SQL Azure.

Osteoblast

CSV --> local DB --> remote DB sounds painful. Do you really have to have an intermediate local DB? Is there a direct-to-Azure example? – Florance

@Florance it is much easier way to do it instead of dealing with all these command lines Microsoft suggests. – Grosswardein

I don't see why the local DB is required. Why not just bulk insert straight to SQL Azure? – Nyssa

Answer 3

1. In SSMS, right click the database, and choose Tasks > Import Flat File.

2. Follow the self-explanatory Wizard prompts. SSMS will guess at the field types, and you'll have a chance to change the types before importing.

The flat file will be imported into a newly created table.

This works well when the database is an Azure SQL Database using Entra authentication, because no connection details are required by the Flat File wizard.

By contrast, if you use Import Data, instead of Import Flat File, then you'll need to enter connection details, including username and password, and Entra isn't available.

Subdivision