sql server 2005 T-SQL BULK INSERT (Transact-SQL)

最新推荐文章于 2023-09-12 11:41:23 发布

czh_liyu

最新推荐文章于 2023-09-12 11:41:23 发布

阅读量5.9k

点赞数

分类专栏： SQL SERVER 文章标签： sql server insert constraints triggers float schema

本文链接：https://blog.csdn.net/czh_liyu/article/details/1956980

版权

SQL SERVER 专栏收录该内容

102 篇文章 0 订阅

订阅专栏

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

主题链接图标 Transact-SQL 语法约定

语法

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) 名称。

BATCHSIZE = batch_size

指定批处理中的行数。每个批处理作为一个事务复制至服务器。如果复制操作失败，则 SQL Server 提交或回滚每个批处理的事务。默认情况下，指定数据文件中的所有数据为一个批处理。

有关详细信息，请参阅管理大容量导入的批处理。

CHECK_CONSTRAINTS

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

注意：
始终强制使用 UNIQUE、PRIMARY KEY 和 NOT NULL 约束。

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

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

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

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

有关详细信息，请参阅通过大容量导入操作控制约束检查。

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | ' code_page ' }

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

CODEPAGE 值	说明
ACP	char、varchar 或 text 数据类型的列从 ANSI/Microsoft Windows 代码页 (ISO 1252) 转换为 SQL Server 代码页。
OEM（默认值）	char、varchar 或 text 数据类型的列从系统 OEM 代码页转换为 SQL Server 代码页。
RAW	不进行从一个代码页到另一个代码页的转换；这是最快的选项。
code_page	特定的代码页码，例如 850。

有关详细信息，请参阅在不同排序规则间复制数据。

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

指定 BULK INSERT 使用指定的数据文件类型值执行导入操作。

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

FIELDTERMINATOR = ' field_terminator '

指定要用于 char 和 widechar 数据文件的字段终止符。默认的字段终止符是 /t（制表符）。有关详细信息，请参阅指定字段终止符和行终止符。

FIRSTROW = first_row

指定要加载的第一行的行号。默认值是指定数据文件中的第一行。

注意：
FIRSTROW 属性不可用于跳过列标题。跳过行时，SQL Server 数据库引擎只考虑字段终止符，而不考虑所跳过行的字段中的数据。

FIRE_TRIGGERS

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

如果没有指定 FIRE_TRIGGERS，将不执行任何插入触发器。

有关详细信息，请参阅导入大容量数据时控制触发器执行。

FORMATFILE = ' format_file_path '

指定一个格式化文件的完整路径。格式化文件用于说明包含存储响应的数据文件，这些存储响应是使用 bcp 实用工具在相同的表或视图中创建的。在下列情况下应使用格式化文件：

数据文件包含的列多于或少于表或视图包含的列。
列的顺序不同。
列分隔符发生变化。
数据格式有其他更改。通常，使用 bcp 实用工具创建格式化文件并根据需要用文本编辑器进行修改。有关详细信息，请参阅bcp 实用工具。

KEEPIDENTITY

指定导入数据文件中的标识值用于标识列。如果没有指定 KEEPIDENTITY，则此列的标识值可被验证但不能导入，并且 SQL Server 将根据表创建时指定的种子值和增量值自动分配一个唯一的值。如果数据文件不包含该表或视图中标识列的值，请使用一个格式化文件指定在导入数据时表或视图中的标识列被忽略；SQL Server 自动为此列分配唯一的值。有关详细信息，请参阅 DBCC CHECKIDENT (Transact-SQL)。

有关保留标识值的详细信息，请参阅大容量导入数据时保留标识值。

KEEPNULLS

指定在大容量导入操作期间空列应保留一个空值，而不插入用于列的任何默认值。有关详细信息，请参阅在大容量导入期间保留空值或使用默认值。

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 还将验证导入的数据是否已排序。

有关详细信息，请参阅大容量导入数据时控制排序顺序。

指示可以指定多个列的占位符。

ROWS_PER_BATCH = rows_per_batch

指示数据文件中近似的数据行数量。

默认情况下，数据文件中所有的数据都作为单一事务发送到服务器，批处理中的行数对于查询优化器是未知的。如果指定了 ROWS_PER_BATCH（其值 > 0），则服务器将使用该值优化大容量导入操作。为 ROWS_PER_BATCH 指定的值应当与实际行数大致相同。

有关详细信息，请参阅管理大容量导入的批处理。

ROWTERMINATOR = ' row_terminator '

指定对于 char 和 widechar 数据文件要使用的行终止符。默认行终止符为 /r/n（换行符）。有关详细信息，请参阅指定字段终止符和行终止符。

TABLOCK

指定为大容量导入操作持续时间获取一个表级锁。如果表没有索引并且指定了 TABLOCK，则该表可以同时由多个客户端加载。默认情况下，锁定行为由表选项 table lock on bulk load 确定。在大容量导入操作期间持有锁会减少表上的锁争用，从而显著提高操作性能。

有关详细信息，请参阅控制大容量导入的锁定行为。

ERRORFILE = ' file_name '

指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。这些行将按原样从数据文件复制到此错误文件中。

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

备注

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

BULK INSERT 语句能在用户定义事务中执行。对使用 BULK INSERT 语句和 BATCHSIZE 子句将数据导入表或视图（使用多个批处理）的用户定义事务执行回滚操作，将回滚所有发送给 SQL Server 的批处理。

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

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

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

无效数据的形式在 SQL Server 的早期版本中可以大容量导入，但现在可能无法加载。在 SQL Server 的早期版本中，客户端尝试访问无效数据时才会出错。在大容量导入后查询数据时，由 SQL Server 2005 强制使用的更为严格的验证可最大程度地减少意外情况发生。

大容量导出或导入 SQLXML 文档

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

数据类型	结果
SQLCHAR 或 SQLVARYCHAR	在客户端代码页或排序规则隐含的代码页中发送数据。效果等同于在不指定格式化文件的情况下指定 DATAFILETYPE = 'char'。
SQLNCHAR 或 SQLNVARCHAR	以 Unicode 格式发送数据。效果等同于在不指定格式化文件的情况下指定 DATAFILETYPE = 'widechar'。
SQLBINARY 或 SQLVARYBIN	不经任何转换即发送数据。

字符串到小数的类型转换

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

注意：
在 SQL Server 版本 7.0 和 SQL Server 2000 中，BULK INSERT 支持将使用科学计数法表示数值的字符串从字符串类型转换为小数类型。

若要在 SQL Server 2005 中实现此行为，请使用格式化文件将科学记数法 float 数据大容量导入小数列中。在格式化文件中，显式说明列作为 real 或 float 数据。有关这些数据类型的详细信息，请参阅 float 和 real (Transact-SQL)。

注意：
格式化文件表示 real 数据作为 SQLFLT4 数据类型，以及 float 数据作为 SQLFLT8 数据类型。有关 XML 格式化文件的详细信息，请参阅 XML 格式化文件的架构语法；有关非 XML 格式化文件的信息，请参阅使用 bcp 指定文件存储类型。

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

该示例使用下表：

复制代码

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"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="/t" MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="/r/n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

若要使用此格式化文件（使用文件名 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

权限

需要 INSERT 和 ADMINISTER BULK OPERATIONS 权限。另外，当下列各项中的一项或多项为真时，还需要 ALTER TABLE 权限：

约束存在并且未指定 CHECK_CONSTRAINTS 选项。

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

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

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

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

安全帐户委托（模拟）

如果 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...) 导入大容量数据。

示例

A. 使用管道从文件导入数据

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

复制代码

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

B. 使用 FIRE_TRIGGER 参数

以下示例指定 FIRE_TRIGGERS 参数。

复制代码

BULK INSERT AdventureWorks.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 AdventureWorks.Sales.SalesOrderDetail
FROM ''<drive>:/<path>/<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

其他示例

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

请参阅

参考

OPENROWSET (Transact-SQL)
sp_tableoption (Transact-SQL)

其他资源

准备用于大容量导出或大容量导入的数据
用于导入或导出数据的数据格式
用来导入或导出数据的格式化文件
优化大容量导入性能
关于大容量导入和大容量导出操作
大容量导入和导出数据的方案
bcp 实用工具
使用 SQL 排序规则
使用表级锁定并行导入数据

czh_liyu

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql server 2005 T-SQL BULK INSERT (Transact-SQL)

以用户指定的格式将数据文件导入数据库表或视图。 Transact-SQL 语法约定语法 BULK INSERT [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] FROM data_file [ WITH
复制链接

扫一扫