将数据从 CSV 加载到 Azure SQL 数据库或 SQL 托管实例(平面文件)Load data from CSV into Azure SQL Database or SQL Managed Instance (flat files)

适用于: Azure SQL 数据库 Azure SQL 托管实例

可以使用 bcp 命令行实用程序将数据从 CSV 文件导入 Azure SQL 数据库或 Azure SQL 托管实例。You can use the bcp command-line utility to import data from a CSV file into Azure SQL Database or Azure SQL Managed Instance.

准备阶段Before you begin

先决条件Prerequisites

若要完成本文中的步骤,需要:To complete the steps in this article, you need:

  • Azure SQL 数据库中的数据库A database in Azure SQL Database
  • 已安装 bcp 命令行实用工具The bcp command-line utility installed
  • 已安装 sqlcmd 命令行实用工具The sqlcmd command-line utility installed

可以从 Microsoft 下载中心下载 bcp 和 sqlcmd 实用程序。You can download the bcp and sqlcmd utilities from the Microsoft Download Center.

采用 ASCII 或 UTF-16 格式的数据Data in ASCII or UTF-16 format

如果使用自己的数据尝试学习本教程,则数据需要使用 ASCII 或 UTF-16 编码,因为 bcp 不支持 UTF-8。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.

1.创建目标表1. Create a destination table

将 SQL 数据库中的某个表定义为目标表。Define a table in SQL Database as the destination table. 该表中的列必须对应于数据文件每一行中的数据。The columns in the table must correspond to the data in each row of your data file.

若要创建表,请打开命令提示符并使用 sqlcmd.exe 运行以下命令: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
    )
    ;
"

2.创建源数据文件2. Create a source data file

打开记事本,将以下几行数据复制到新文本文件,然后将此文件保存到本地临时目录 C:\Temp\DimDate2.txt。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. 此数据采用 ASCII 格式。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

(可选)若要从 SQL Server 数据库导出自己的数据,请打开命令提示符并运行以下命令。(Optional) To export your own data from a SQL Server database, open a command prompt and run the following command. 将 TableName、ServerName、DatabaseName、Username 和 Password 替换成自己的信息。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 ,

3.加载数据3. Load the data

要加载数据,请打开命令提示符并运行以下命令,请注意将 Server Name、Database Name、Username 和 Password 替换为你自己的信息。To load the data, open a command prompt and run the following command, replacing the values for Server Name, Database name, 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:

DateIdDateId CalendarQuarterCalendarQuarter FiscalQuarterFiscalQuarter
2015010120150101 11 33
2015020120150201 11 33
2015030120150301 11 33
2015040120150401 22 44
2015050120150501 22 44
2015060120150601 22 44
2015070120150701 33 11
2015080120150801 33 11
2015080120150801 33 11
2015100120151001 44 22
2015110120151101 44 22
2015120120151201 44 22

后续步骤Next steps

若要迁移 SQL Server 数据库,请参阅 SQL Server 数据库迁移To migrate a SQL Server database, see SQL Server database migration.