Load data from CSV into Azure SQL Database or SQL Managed Instance (flat files)

Applies to: Azure SQL Database Azure SQL Managed Instance

You can use the bcp command-line utility to import data from a CSV file into Azure SQL Database or Azure SQL Managed Instance.

To complete the steps in this article, you need:

• A database
• The bcp command-line utility installed
• The sqlcmd command-line utility installed

You can download the bcp and sqlcmd utilities from the sqlcmd Documentation.

If you are trying this tutorial with your own data, your data needs to use the ASCII or UTF-16 encoding since bcp does not support UTF-8.

Define a table as the destination table. The columns in the table must correspond to the data in each row of your data file.

To create a table, open a command prompt and use sqlcmd.exe to run the following command:

sqlcmd.exe -S <server name> -d <database name> -U <username> -P <password> -I -Q "
    CREATE TABLE DimDate2
    (
        DateId INT NOT NULL,
        CalendarQuarter TINYINT NOT NULL,
        FiscalQuarter TINYINT NOT NULL
    )
    ;
"

Open Notepad and copy the following lines of data into a new text file and then save this file to your local temp directory, C:\Temp\DimDate2.txt. This data is in ASCII format.

20150301,1,3
20150501,2,4
20151001,4,2
20150201,1,3
20151201,4,2
20150801,3,1
20150601,2,4
20151101,4,2
20150401,2,4
20150701,3,1
20150901,3,1
20150101,1,3

(Optional) To export your own data from a SQL Server database, open a command prompt and run the following command. Replace <TableName>, <ServerName>, <DatabaseName>, <Username>, and <Password> with your own information.

bcp <TableName> out C:\Temp\DimDate2_export.txt -S <ServerName> -d <DatabaseName> -U <Username> -P <Password> -q -c -t ","

To load the data, open a command prompt and run the following command, replacing the values for <ServerName>, <DatabaseName>, <Username>, and <Password> with your own information.

bcp DimDate2 in C:\Temp\DimDate2.txt -S <ServerName> -d <DatabaseName> -U <Username> -P <password> -q -c -t ","

Use this command to verify the data was loaded properly:

sqlcmd.exe -S <server name> -d <database name> -U <username> -P <password> -I -Q "SELECT * FROM DimDate2 ORDER BY 1;"

The results should look like this: