BULK INSERT (Transact-SQL)

项目
02/18/2014

以用户指定的格式将数据文件导入到数据库表或视图中。

语法

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )]

参数

database_name
指定的表或视图所在的数据库的名称。如果未指定，则默认为当前数据库。
schema_name
表或视图架构的名称。如果用户执行大容量导入操作的默认架构为指定表或视图的架构，则 schema_name 是可选的。如果未指定 schema 并且用户执行大容量导入操作的默认架构与指定表或视图的架构不同，则 SQL Server 将返回一条错误消息，同时将取消大容量导入操作。
table_name
要将数据大容量导入其中的表或视图的名称。只能使用所有列均引用相同基表的视图。有关向视图中加载数据的限制的详细信息，请参阅 INSERT (Transact-SQL)。
' data_file '
数据文件的完整路径，该数据文件包含要导入到指定表或视图中的数据。使用 BULK INSERT 可以从磁盘（包括网络、软盘、硬盘等）导入数据。

data_file 必须基于运行 SQL Server 的服务器指定一个有效路径。如果 data_file 为远程文件，则指定通用命名约定 (UNC) 名称。 UNC 名称采用以下格式：\\Systemname\ShareName\Path\FileName。例如，\\SystemX\DiskZ\Sales\update.txt。
BATCHSIZE **=**batch_size
指定批处理中的行数。每个批处理作为一个事务复制到服务器。如果复制操作失败，则 SQL Server 将提交或回滚每个批处理的事务。默认情况下，指定数据文件中的所有数据为一个批处理。有关性能注意事项的信息，请参阅本主题后面的“备注”。

CHECK_CONSTRAINTS
指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。若没有 CHECK_CONSTRAINTS 选项，则忽略所有 CHECK 和 FOREIGN KEY 约束，并在该操作后将表的约束标记为不可信。

注意
始终强制使用 UNIQUE 和 PRIMARY KEY 约束。导入使用 NOT NULL 约束定义的字符列时，当文本文件中没有值时，BULK INSERT 插入一个空白字符串。

有时必须检查针对整个表的约束。执行大容量导入操作之前，如果表不为空，则重新验证约束的代价可能会超出对增量数据应用 CHECK 约束的代价。

当输入数据包含违反约束的行时，您可能希望禁用约束（默认行为）。禁用 CHECK 约束后，您可以导入数据并使用 Transact-SQL 语句删除无效数据。

注意
MAXERRORS 选项不适用于约束检查。

注意
在 SQL Server 2005 和更高版本中，BULK INSERT 强制执行新的数据验证和数据检查，如果对某个数据文件中的无效数据执行这样的验证和检查，则可能导致现有脚本失败。

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
指定该数据文件中数据的代码页。仅当数据含有字符值大于 127 或小于 32 的 char、varchar 或 text 列时，CODEPAGE 才适用。

注意
Microsoft 建议在格式化文件中为每个列指定一个排序规则名称。

ACP

char、varchar 或 text 数据类型的列从 ANSI/Microsoft Windows 代码页 (ISO 1252) 转换为 SQL Server 代码页。

OEM（默认值）

char、varchar 或 text 数据类型的列从系统 OEM 代码页转换为 SQL Server 代码页。

RAW

不进行从一个代码页到另一个代码页的转换；这是最快的选项。

code_page

特定的代码页码，例如 850。

重要提示
SQL Server 不支持代码页 65001（UTF-8 编码）。

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }
指定 BULK INSERT 使用指定的数据文件类型值执行导入操作。

DATAFILETYPE 值	所有数据都表示为：
char（默认值）	字符格式。有关详细信息，请参阅使用字符格式导入或导出数据 (SQL Server)。
native	本机（数据库）数据类型。通过使用 bcp 实用工具从 SQL Server 大容量导入数据来创建本机数据文件。与 char 值相比，本机值提供更高的性能。有关详细信息，请参阅使用本机格式导入或导出数据 (SQL Server)。
widechar	Unicode 字符。有关详细信息，请参阅使用 Unicode 字符格式导入或导出数据 (SQL Server)。
widenative	本机（数据库）数据类型，除了 char、varchar 和 text 列以外，列中的数据均存储为 Unicode 格式。通过使用 bcp 实用工具从 SQL Server 大容量导入数据来创建 widenative 数据文件。与 widechar 相比，widenative 值可以提供更高的性能。如果数据文件包含 ANSI 扩展字符，则指定 widenative。有关详细信息，请参阅使用 Unicode 本机格式导入或导出数据 (SQL Server)。

FIELDTERMINATOR ='field_terminator'
指定要用于 char 和 widechar 数据文件的字段终止符。默认的字段终止符是 \t（制表符）。有关详细信息，请参阅指定字段终止符和行终止符 (SQL Server)。

FIRSTROW **=**first_row
指定要加载的第一行的行号。默认值是指定数据文件中的第一行。 FIRSTROW 从 1 开始。

注意
FIRSTROW 属性不可用于跳过列标题。 BULK INSERT 语句不支持跳过标题。跳过行时，SQL Server 数据库引擎只考虑字段终止符，而不会对所跳过行的字段中的数据进行验证。

FIRE_TRIGGERS
指定将在大容量导入操作期间执行目标表中定义的所有插入触发器。如果为针对目标表的 INSERT 操作定义了触发器，则每次完成批处理操作时均激发触发器。

如果没有指定 FIRE_TRIGGERS，将不执行任何插入触发器。
FORMATFILE ='format_file_path'
指定格式化文件的完整路径。格式化文件用于说明包含存储响应的数据文件，这些存储响应是使用 bcp 实用工具在相同的表或视图中创建的。在下列情况下应使用格式化文件：
- 数据文件包含的列多于或少于表或视图包含的列。
- 列的顺序不同。
- 列分隔符不同。
- 数据格式有其他更改。格式化文件通常使用 bcp 实用工具来创建，并可根据需要用文本编辑器进行修改。有关详细信息，请参阅 bcp 实用工具。
KEEPIDENTITY
指定导入数据文件中的标识值用于标识列。如果没有指定 KEEPIDENTITY，则此列的标识值可被验证但不能导入，并且 SQL Server 将根据创建表的过程中指定的种子值和增量值自动分配唯一值。如果数据文件不包含该表或视图中标识列的值，请使用格式化文件指定在导入数据时跳过表或视图中的标识列；SQL Server 会自动为该列分配唯一的值。有关详细信息，请参阅 DBCC CHECKIDENT (Transact-SQL)。

有关保留标识值的详细信息，请参阅大容量导入数据时保留标识值 (SQL Server)。
KEEPNULLS
指定空列在大容量导入操作期间应保留 Null 值，而不插入列的任何默认值。有关详细信息，请参阅在大容量导入期间保留 Null 或使用默认值 (SQL Server)。
KILOBYTES_PER_BATCH = kilobytes_per_batch
将每个批处理中数据的近似千字节数 (KB) 指定为 kilobytes_per_batch。默认情况下，KILOBYTES_PER_BATCH 是未知的。有关性能注意事项的信息，请参阅本主题后面的“备注”。
LASTROW**=**last_row
指定要加载的最后一行的行号。默认值为 0，表示指定数据文件中的最后一行。
MAXERRORS = max_errors
指定允许在数据中出现的最大语法错误数，超过该数量后将取消大容量导入操作。大容量导入操作无法导入的每一行都将被忽略并且计为一个错误。如果未指定 max_errors，则默认值为 10。

注意

MAX_ERRORS 选项不适用于约束检查，也不适用于转换 money 和 bigint 数据类型。
ORDER ( { column [ ASC | DESC ] } [ ,...n ] )
指定如何对数据文件中的数据排序。如果根据表中的聚集索引（如果有）对要导入的数据排序，则可提高大容量导入的性能。如果数据文件按不同于聚集索引键的顺序排序，或者该表没有聚集索引，则忽略 ORDER 子句。提供的列名必须是目标表中有效的列名。默认情况下，大容量插入操作假设数据文件未排序。对于经过优化的大容量导入，SQL Server 还将验证导入的数据是否已排序。
n
指示可以指定多个列的占位符。
ROWS_PER_BATCH **=**rows_per_batch
指示数据文件中近似的数据行数量。

默认情况下，数据文件中所有的数据都作为单一事务发送到服务器，批处理中的行数对于查询优化器是未知的。如果指定了 ROWS_PER_BATCH（值 > 0），则服务器将使用此值优化大容量导入操作。为 ROWS_PER_BATCH 指定的值应当与实际行数大致相同。有关性能注意事项的信息，请参阅本主题后面的“备注”。
ROWTERMINATOR ='row_terminator'
指定要用于 char 和 widechar 数据文件的行终止符。默认行终止符为 \r\n（换行符）。有关详细信息，请参阅指定字段终止符和行终止符 (SQL Server)。
TABLOCK
指定在大容量导入操作持续时间内获取一个表级锁。如果表没有索引并且指定了 TABLOCK，则该表可以同时由多个客户端加载。默认情况下，锁定行为由表选项 table lock on bulk load 决定。通过在大容量导入操作期间保留锁，可减少对表争用锁的情况，有时可显著提高性能。有关性能注意事项的信息，请参阅本主题后面的“备注”。
ERRORFILE ='file_name'
指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。这些行将按原样从数据文件复制到此错误文件中。

错误文件是执行命令时创建的。如果该文件已经存在，则会发生错误。此外，还创建了一个扩展名为 .ERROR.txt 的控制文件。此文件引用错误文件中的每一行并提供错误诊断。纠正错误后即可加载数据。

注意
MAX_ERRORS 选项不适用于约束检查，也不适用于转换 money 和 bigint 数据类型。

兼容性

BULK INSERT 将对从文件中读取的数据执行严格的数据验证和数据检查，在对无效数据执行这样的验证和检查时，可能导致现有脚本失败。例如，BULK INSERT 验证：

float 或 real 数据类型的本机表示形式是否有效。
Unicode 数据的字节数是否为偶数。

数据类型

字符串到小数的数据类型转换

BULK INSERT 中采用的字符串到小数的数据类型转换与 Transact-SQL CONVERT 函数遵循相同的规则，该函数拒绝使用科学记数法表示数值的字符串。因此，BULK INSERT 将此类字符串视为无效值并报告转换错误。

若要绕过此行为，请使用格式化文件将以科学记数法表示的 float 数据大容量导入到小数列中。在格式化文件中，请将列显式描述为 real 或 float 数据。有关这些数据类型的详细信息，请参阅 float 和 real (Transact-SQL)。

注意
格式化文件将 real 数据表示为 SQLFLT4 数据类型，并将 float 数据表示为 SQLFLT8 数据类型。有关非 XML 格式化文件的信息，请参阅使用 bcp 指定文件存储类型 (SQL Server)。

导入使用科学记数法的数值的示例

此示例使用下表：

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

用户要将数据大容量导入 t_float 表中。数据文件 C:\t_float-c.dat 包含以科学记数法表示的 float 数据，例如：

8.0000000000000002E-28.0000000000000002E-2

但是，BULK INSERT 无法将此数据直接导入 t_float，原因是其第二个列 c2 使用的是 decimal 数据类型。因此，必须使用格式化文件。格式化文件必须将以科学记数法表示的 float 数据映射到列 c2 的小数格式。

以下格式化文件使用 SQLFLT8 数据类型将第二个数据字段映射到第二列：

<?xml version="1.0"?>

若要使用此格式化文件（使用文件名 C:\t_floatformat-c-xml.xml）将测试数据导入测试表中，请发出下列 Transact-SQL 语句：

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

用于大容量导出或导入 SQLXML 文档的数据类型

若要大容量导出或导入 SQLXML 数据，请在格式化文件中使用下列数据类型之一：

数据类型	效果
SQLCHAR 或 SQLVARCHAR	在客户端代码页或排序规则隐含的代码页中发送数据。其效果与指定 DATAFILETYPE = 'char' 而不指定格式化文件相同。
SQLNCHAR 或 SQLNVARCHAR	以 Unicode 格式发送数据。其效果与指定 DATAFILETYPE = 'widechar' 而不指定格式化文件相同。
SQLBINARY 或 SQLVARBIN	不经任何转换即发送数据。

一般备注

有关 BULK INSERT 语句、INSERT ... SELECT * FROM OPENROWSET(BULK...) 语句以及 bcp 命令的比较，请参阅大容量导入和导出数据 (SQL Server)。

有关为大容量导入准备数据的信息，请参阅准备用于大容量导出或导入的数据 (SQL Server)。

BULK INSERT 语句可在用户定义的事务内执行，以便将数据导入到表或视图中。或者，为了将多个匹配项用于大容量导入数据，事务可以在 BULK INSERT 语句中指定 BATCHSIZE 子句。如果回滚某一多批处理事务，则回滚该事务已发送到 SQL Server 的每个批处理。

互操作性

从 CSV 文件导入数据

逗号分隔值 (CSV) 文件不受 SQL Server 大容量导入操作支持。但是，在某些情况下，CSV 文件可在将数据大容量导入 SQL Server 时用作数据文件。有关从 CSV 数据文件导入数据的要求的信息，请参阅准备用于大容量导出或导入的数据 (SQL Server)。

日志记录行为

有关何时在事务日志中记录由大容量导入执行的行插入操作的信息，请参阅在大容量导入中按最小方式记录日志的前提条件。

限制

将格式文件用于 BULK INSERT 时，最多只能指定 1024 个字段。这与表中允许的最大列数相同。如果将 BULK INSERT 与包含 1024 个字段以上的数据文件一起使用，BULK INSERT 将生成 4822 错误。 bcp 实用工具没有此限制，因此对于包含 1024 个以上字段的数据文件，请使用 bcp 命令。

性能注意事项

如果要在单次批处理中刷新的页数超过了内部阈值，则可能会对缓冲池执行完全扫描，以识别要在批处理提交时刷新的页面。此完全扫描可能会降低大容量导入操作的性能。在将大型缓冲池与较慢的 I/O 子系统结合使用时，就可能出现超过内部阈值的情况。若要避免大型机上的缓冲区溢出，请不要使用 TABLOCK 提示（将删除大容量优化），也不要使用较小的批大小（将保留大容量优化）。

由于计算机千差万别，因此我们建议在数据加载过程中测试各种批大小，以确定最佳方案。

安全性

安全帐户委托（模拟）

如果用户使用的是 SQL Server 登录名，则系统将使用 SQL Server 进程帐户的安全配置文件。使用 SQL Server 身份验证的登录名不能在数据库引擎外部进行身份验证。因此，当 BULK INSERT 命令由使用 SQL Server 身份验证的登录名启动时，使用 SQL Server 进程帐户（SQL Server 数据库引擎服务使用的帐户）的安全上下文建立到数据的连接。要成功读取源数据，您必须授予 SQL Server 数据库引擎使用的帐户访问源数据的权限。与此相反，如果 SQL Server 用户使用 Windows 身份验证登录，则该用户只能读取用户帐户可以访问的那些文件，而不考虑 SQL Server 进程的安全配置文件。

如果在一台计算机上使用 sqlcmd 或 osql 执行 BULK INSERT 语句，将数据插入第二台计算机上的 SQL Server，同时使用 UNC 路径指定位于第三台计算机上的 data_file，则可能会收到 4861 错误。

若要解决此问题，请使用 SQL Server 身份验证并指定一个使用 SQL Server 进程帐户安全配置文件的 SQL Server 登录名，或者对 Windows 进行配置以启用安全帐户委托。有关如何使用户帐户可信以进行委托的信息，请参阅 Windows 帮助。

有关安全帐户委托以及使用 BULK INSERT 的其他安全注意事项的详细信息，请参阅使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据(SQL Server)。

权限

需要 INSERT 和 ADMINISTER BULK OPERATIONS 权限。另外，如果存在下列一种或多种情况，则还需要 ALTER TABLE 权限：

存在约束但未指定 CHECK_CONSTRAINTS 选项。

注意

禁用约束是默认行为。若要显式检查约束，请使用 CHECK_CONSTRAINTS 选项。
存在触发器但未指定 FIRE_TRIGGER 选项。

注意

默认情况下，不激发触发器。若要显式激发触发器，请使用 FIRE_TRIGGER 选项。
使用 KEEPIDENTITY 选项可以从数据文件中导入标识值。

注意
禁用约束是默认行为。若要显式检查约束，请使用 CHECK_CONSTRAINTS 选项。

注意
默认情况下，不激发触发器。若要显式激发触发器，请使用 FIRE_TRIGGER 选项。

示例

A.使用竖线从文件导入数据

下面的示例将订单详细信息从指定的数据文件导入 AdventureWorks.Sales.SalesOrderDetail 表中，方法是使用竖线 (|) 作为字段终止符，并使用 |\n 作为行终止符。

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      );

B.使用 FIRE_TRIGGER 参数

下面的示例指定 FIRE_TRIGGERS 参数。

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =' |',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      );

C.使用换行符作为行终止符

下面的示例将导入使用换行符作为行终止符的文件（如 UNIX 输出）：

DECLARE @bulk_cmd varchar(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')';
EXEC(@bulk_cmd);

注意
根据 Microsoft Windows 处理文本文件的方式，(\n 自动使用 \r\n) 来替换。

其他示例

以下主题提供了其他 BULK INSERT 示例：

请参阅

参考

bcp 实用工具

INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

sp_tableoption (Transact-SQL)

概念

大容量导入和导出数据 (SQL Server)

用来导入或导出数据的格式化文件 (SQL Server)

Share via