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.
6
votes
Import into a local database, have SSMS generate the create table script, run that on the Azure db, and then do an INSERT?
– Jim
@Jim I am running to this same issue. How do you run this table script on Azure db to do an INSERT
– Salton
@Jim can it be done without an extra DB?
– Hack-R
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.McDermaid
2 Answers
2
votes
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.
0
votes
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.
