All geek questions in one place

Import CSV to Azure SQL Database using SSMS

I have a set of large CSV files with many columns that I need to import into an Azure SQL database. I usually use the import wizard in SQL Server Management Studio. However, when connecting to SQL Azure in SSMS, the wizard does not look like an option. It's right? And if so, what is the recommended tool for this task? I am looking for a tool that will infer from the data which columns should allow me to override the data type as needed. Since I have many columns in each of the files, I would like to avoid the tedious work of manually writing SQL code to generate tables.

+4
source share
2 answers

When you transfer any data to an SQL database, the data must be structured. The processes are to convert the CSV to a table structure and then transfer it directly to SQL Azure. In fact, you can write a stored procedure in SSMS to do it all in one.

Since the CSV file can be a tab, comma, or any other character, you can perform a bulk insert in the local database first, as described here, and then synchronize the table with SQL Azure.

0
source

This worked for me:

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

Sometimes I get errors with CSV files, but it is enough to use the Excel file or check the parameters of the CSV data columns in the Import Wizard.

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

They could / should have made it easier, for example, inserting SFTP + or importing the GUI directly into Azure SQL, for example, in Hue.

0
source

Source: https://habr.com/ru/post/1435154/

More articles:


All Articles