准备用于批量导出或导入的数据Prepare data for bulk export or import

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

本部分讨论计划大容量导出操作时的相关注意事项以及大容量导入操作的要求。This section discusses the considerations involved in planning for bulk-export operations and the requirements for bulk-import operations.

备注

如果你不确定应如何针对批量导入设置数据文件的格式,则可以使用 bcp 实用工具将数据从表导出到数据文件中。If you are uncertain about how to format a data file for bulk importing, use the bcp utility to export data from the table into a data file. 此文件中每个数据字段的格式均显示了将数据大容量导入对应表列时所要求的格式。The formatting of each data field in this file shows the formatting required to bulk import data into the corresponding table column. 对数据文件的各个字段使用相同的数据格式。Use the same data formatting for fields of your data file.

大容量导出的数据文件格式注意事项Data-File Format Considerations for Bulk Export

在使用 bcp 命令执行批量导出操作之前,请先考虑下列事项:Before you perform a bulk-export operation by using the bcp command, consider the following:

  • 将数据导出到文件时, bcp 命令使用指定的文件名自动创建数据文件。When data is exported to a file, the bcp command creates the data file automatically by using the specified file name. 如果该文件名已经存在,正在大容量复制到数据文件的数据将覆盖文件中的现有内容。If that file name is already in use, the data that is being bulk copied to the data file overwrites the existing contents of the file.

  • 从表或视图大容量导出到数据文件要求对正在大容量复制的表或视图具有 SELECT 权限。Bulk export from a table or view to a data file requires SELECT permission on the table or view that is being bulk copied.

  • MicrosoftMicrosoft SQL ServerSQL Server 可以使用并行扫描检索数据。SQL ServerSQL Server can use parallel scans to retrieve data. 因此,通常不保证从 SQL ServerSQL Server 实例大容量导出的表行在数据文件中按特定顺序排列。Therefore, the table rows that are bulk exported in from an instance of SQL ServerSQL Server are not ordinarily guaranteed to be in any specific order in the data file. 为了确保批量导出的表行在数据文件中按特定顺序排列,请使用 queryout 选项来通过查询进行批量导出,并指定一个 ORDER BY 子句。To make bulk-exported table rows appear in a specific order in the data file, use the queryout option to bulk export from a query, and specify an ORDER BY clause.

大容量导入的数据文件格式要求Data-File Format Requirements for Bulk Import

为了导入数据文件中的数据,该文件必须满足以下基本要求:To import data from a data file, the file must meet the following basic requirements:

  • 数据必须以行和列的格式表示。The data must be in row and column format.

备注

数据文件的结构不必与 SQL ServerSQL Server 表的结构一致,因为大容量导入过程中可以跳过列或对列重新排序。The structure of the data file does not need to be identical to the structure of the SQL ServerSQL Server table because columns can be skipped or reordered during the bulk-import process.

  • 数据文件中的数据格式必须是支持的格式,例如字符格式或本机格式。The data in the data file must be in a supported format such as character or native format.

  • 数据可以是字符格式或本机二进制格式(包括 Unicode)。The data can be in character or native binary format including Unicode.

  • 若要使用 bcp 命令、BULK INSERT 语句或 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句导入数据,目标表必须已经存在。To import data by using a bcp command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, the destination table must already exist.

  • 数据文件中的每个字段都必须与目标表中的对应列兼容。Each field in the data file must be compatible with the corresponding column in the target table. 例如,不能将 int 字段加载到 datetime 列。For example, an int field cannot be loaded into a datetime column. 有关详细信息,请参阅用于批量导入或导出的数据格式 (SQL Server)在使用 bcp 时指定数据格式以获得兼容性 (SQL Server)For more information, see Data Formats for Bulk Import or Bulk Export (SQL Server) and Specify Data Formats for Compatibility when Using bcp (SQL Server).

    备注

    若要指定从数据文件导入的行子集而并非整个文件,可以使用带有 -F first_row 开关和/或 -L last_row 开关的 bcp 命令。To specify a subset of rows to import from a data file rather than the entire file, you can use a bcp command with the -F first_row switch and/or -L last_row switch. 有关详细信息,请参阅 bcp UtilityFor more information, see bcp Utility.

  • 若要从包含固定长度或固定宽度字段的数据文件导入数据,请使用格式化文件。To import data from data files with fixed-length or fixed-width fields, use a format file. 有关详细信息,请参阅 XML 格式化文件 (SQL Server)For more information, see XML Format Files (SQL Server).

  • 逗号分隔值 (CSV) 文件不受 SQL ServerSQL Server 大容量导入操作支持。Comma-separated value (CSV) files are not supported by SQL ServerSQL Server bulk-import operations. 但是,在某些情况下,CSV 文件可在将数据大容量导入 SQL ServerSQL Server时用作数据文件。However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL ServerSQL Server. 请注意,CSV 文件的字段终止符不一定是逗号。Note that the field terminator of a CSV file does not have to be a comma. 若要能够用作大容量导入的数据文件,CSV 文件必须满足以下限制条件:To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:

    • 数据字段不包含任何字段终止符。Data fields never contain the field terminator.

    • 数据字段中的值可全部使用引号 ("") 引起或全部都不使用引号。Either none or all of the values in a data field are enclosed in quotation marks ("").

    若要从 MicrosoftMicrosoft FoxPro、Visual FoxPro 表 (.dbf) 文件或 Microsoft ExcelMicrosoft Excel 工作表 (.xls) 文件大容量导入数据,需要将数据转换为 CSV 文件以符合前面的限制条件。To bulk import data from a MicrosoftMicrosoft FoxPro or Visual FoxPro table (.dbf) file or a Microsoft ExcelMicrosoft Excel worksheet (.xls) file, you would need to convert the data into a CSV file that complies to the preceding restrictions. 文件扩展名通常将为 .csv。The file extension will typically be .csv. 然后便可以在 SQL ServerSQL Server 大容量导入操作中使用 .csv 文件作为数据文件。You can then use the .csv file as a data file in a SQL ServerSQL Server bulk-import operation.

    在 32 位系统上,通过使用 SQL ServerSQL Server OPENROWSET 和 OLE DB Provider for Jet 可以将 CSV 数据导入 表,但是没有大容量导入优化。On 32-bit systems, it is possible to import CSV data into a SQL ServerSQL Server table without bulk-import optimizations by using OPENROWSET with the OLE DB Provider for Jet. 通过由位于数据源所在目录的 schema.ini 文件定义的架构,Jet 将文本文件作为表处理。Jet treats text files as tables, with the schema defined by a schema.ini file that is located in the same directory as the data source. 对于 CSV 数据,schema.ini 文件内的其中一个参数将是“FORMAT=CSVDelimited”。For a CSV data, one of the parameters in the schema.ini file would be "FORMAT=CSVDelimited". 若要使用此解决方案,需要了解 Jet Test IISAMm 如何工作,例如它的连接字符串语法、schema.ini 用法、注册表设置选项等等。To use this solution, you would need to understand how the Jet Test IISAMm operations-its connection string syntax, schema.ini usage, registry setting options, and so on). 此信息的最佳来源是 Microsoft Access 帮助和知识库 (KB) 文章。The best sources of this information are Microsoft Access Help and Knowledge Base (KB) articles. 有关详细信息,请参阅初始化文本数据源驱动程序如何通过链接服务器使用 SQL Server 7.0 分布式查询来查询安全的 Access 数据库如何使用 Jet OLE DB Provider 4.0 连接到 ISAM 数据库,以及如何使用 Jet Provider 的文本 IIsam 打开带分隔符的文本文件For more information, see Initializing the Text Data Source Driver, How To Use a SQL Server 7.0 Distributed Query with a Linked Server to Secured Access Databases, HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases, and How To Open Delimited Text Files Using the Jet Provider's Text IIsam.

此外,将数据文件中的数据大容量导入表还有以下要求:In addition, the bulk import of data from a data file into a table requires the following:

  • 用户必须对表具有 INSERT 和 SELECT 权限。Users must have INSERT and SELECT permissions on the table. 如果用户使用要求执行数据定义语言 (DDL) 操作(例如禁用约束)的选项时,他们还要具有 ALTER TABLE 权限。Users also need ALTER TABLE permission when they use options that require data definition language (DDL) operations, such as disabling constraints.

  • 使用 BULK INSERT 或 INSERT ... SELECT * FROM OPENROWSET(BULK...) 大容量导入数据时,必须可以通过 SQL ServerSQL Server 进程的安全性配置文件(如果用户使用 SQL ServerSQL Server 提供的登录名进行登录)或在委托安全性下使用的 MicrosoftMicrosoft Windows 登录名对数据文件进行读取操作。When you bulk import data by using BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...), the data file must be accessible for read operations by either the security profile of the SQL ServerSQL Server process (if the user logs in using SQL ServerSQL Server provided login) or by the MicrosoftMicrosoft Windows login that is used under delegated security. 此外,用户还必须具有 ADMINISTER BULK OPERATIONS 权限以读取文件。Additionally, the user must have ADMINISTER BULK OPERATIONS permission to read the file.

备注

由于不支持大容量导入到分区视图,因此无法将数据大容量导入到分区视图。Bulk importing into a partitioned view is unsupported, and attempts to bulk import data into a partitioned view fail.

外部资源External Resources

如何将 Excel 数据导入 SQL ServerHow to import data from Excel to SQL Server

更改历史记录Change History

更新的内容Updated content
添加了有关使用 OLE DB Provider for Jet 导入 CSV 数据的信息。Added information about using the OLE DB Provider for Jet to import CSV data.

另请参阅See Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
数据类型 (Transact-SQL) Data Types (Transact-SQL)
使用字符格式导入或导出数据 (SQL Server) Use Character Format to Import or Export Data (SQL Server)
使用本机格式导入或导出数据 (SQL Server)Use Native Format to Import or Export Data (SQL Server)