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.
4
-
1Import into a local database, have SSMS generate the create table script, run that on the Azure db, and then do an INSERT?Jim– Jim2012-09-19 22:02:25 +00:00Commented Sep 19, 2012 at 22:02
-
@Jim I am running to this same issue. How do you run this table script on Azure db to do an INSERTSalton– Salton2013-08-09 17:26:26 +00:00Commented Aug 9, 2013 at 17:26
-
@Jim can it be done without an extra DB?Hack-R– Hack-R2016-03-08 19:18:02 +00:00Commented Mar 8, 2016 at 19:18
-
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?Nick.Mc– Nick.Mc2019-02-11 02:03:30 +00:00Commented Feb 11, 2019 at 2:03
Add a comment
|
3 Answers
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.
1 Comment
Emil
there is no Import Data option when you use Azure tables. You are probably talking about sql server instance on VMs
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.
3 Comments
Hack-R
CSV --> local DB --> remote DB sounds painful. Do you really have to have an intermediate local DB? Is there a direct-to-Azure example?
Emil
@Hack-R it is much easier way to do it instead of dealing with all these command lines Microsoft suggests.
Nick.Mc
I don't see why the local DB is required. Why not just bulk insert straight to SQL Azure?
In SSMS, right click the database, and choose Tasks > Import Flat File.
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.
