Bulk Insert命令详解
BULK INSERT
以用户指定的格式复制一个数据文件至数据库表或视图中。
语法
BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_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 ] ]
)
]
参数
'database_name'
是包含指定表或视图的数据库的名称。如果未指定，则系统默认为当前数据库。
'owner'
是表或视图所有者的名称。当执行大容量复制操作的用户拥有指定的表或视图时，owner 是可选项。如果没有指定 owner 并且执行大容量复制操作的用户不拥有指定的表或视图，则 Microsoft® SQL Server™ 将返回错误信息并取消大容量复制操作。
'table_name'
是大容量复制数据于其中的表或视图的名称。只能使用那些所有的列引用相同基表所在的视图。有关向视图中复制数据的限制的更多信息，请参见 INSERT。
'data_file'
是数据文件的完整路径，该数据文件包含要复制到指定表或视图的数据。BULK INSERT 从磁盘复制数据（包括网络、软盘、硬盘等）。
data_file 必须从运行 SQL Server 的服务器指定有效路径。如果 data_file 是远程文件，则请指定通用命名规则 (UNC) 名称。
BATCHSIZE [ = batch_size ]
指定批处理中的行数。每个批处理作为一个事务复制至服务器。SQL Server提交或回滚（在失败时）每个批处理的事务。默认情况下，指定数据文件中的所有数据是一个批处理。
CHECK_CONSTRAINTS
指定在大容量复制操作中检查 table_name 的任何约束。默认情况下，将会忽略约束。
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（默认值） 从含有字符数据的数据文件执行大容量复制操作。
native 使用 native（数据库）数据类型执行大容量复制操作。要装载的数据文件由大容量复制数据创建，该复制是用 bcp 实用工具从 SQL Server 进行的。
widechar 从含有 Unicode 字符的数据文件中执行大容量复制操作。
widenative 执行与 native 相同的大容量复制操作，不同之处是 char、varchar 和 text 列在数据文件中存储为 Unicode。要装载的数据文件由大容量复制数据创建，该复制是用 bcp 实用工具从 SQL Server 进行的。该选项是对 widechar 选项的一个更高性能的替代，并且它用于使用数据文件从一个运行 SQL Server 的计算机向另一个计算机传送数据。当传送含有 ANSI 扩展字符的数据时，使用该选项以便利用 native 模式的性能。
FIELDTERMINATOR [ = 'field_terminator' ]
指定用于 char 和 widechar 数据文件的字段终止符。默认的字段终止符是 /t（制表符）。
FIRSTROW [ = first_row ]
指定要复制的第一行的行号。默认值是 1，表示在指定数据文件的第一行。
FIRE_TRIGGERS
指定目的表中定义的任何插入触发器将在大容量复制操作过程中执行。如果没有指定 FIRE_TRIGGERS，将不执行任何插入触发器。
FORMATFILE [ = 'format_file_path' ]
指定一个格式文件的完整路径。格式文件描述了含有存储响应的数据文件，这些存储响应是使用 bcp 实用工具在相同的表或视图中创建的。格式文件应该用于以下情况：
数据文件含有比表或视图更多或更少的列。
列使用不同的顺序。
列分割符发生变化。
数据格式有其它的改变。通常，格式文件通过 bcp 实用工具创建并且根据需要用文本编辑器修改。有关更多信息，请参见 bcp 实用工具。
KEEPIDENTITY
指定标识列的值存在于导入文件中。如果没有指定 KEEPIDENTITY，在导入的数据文件中此列的标识值将被忽略，并且 SQL Server 将根据表创建时指定的种子值和增量值自动赋给一个唯一的值。假如数据文件不含该表或视图中的标识列，使用一个格式文件来指定在导入数据时，表或视图中的标识列应被忽略；SQL Server 自动为此列赋予唯一的值。有关详细信息，请参见 DBCC CHECKIDENT。
KEEPNULLS
指定在大容量复制操作中空列应保留一个空值，而不是对插入的列赋予默认值。
KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]
指定每个批处理中数据的近似千字节数（KB）。默认情况下，KILOBYTES_PER_BATCH 未知。
LASTROW [ = last_row ]
指定要复制的最后一行的行号。默认值是 0，表示指定数据文件中的最后一行。
MAXERRORS [ = max_errors ]
指定在大容量复制操作取消之前可能产生的错误的最大数目。不能被大容量复制操作导入的每一行将被忽略并且被计为一次错误。如果没有指定 max_errors，默认值为 0。
ORDER ( { column [ ASC | DESC ] } [ ,...n ] )
指定数据文件中的数据如何排序。如果装载的数据根据表中的聚集索引进行排序，则可以提高大容量复制操作的性能。如果数据文件基于不同的顺序排序，或表中没有聚集索引，ORDER 子句将被忽略。给出的列名必须是目的表中有效的列。默认情况下，大容量插入操作假设数据文件未排序。
n
是表示可以指定多列的占位符。
ROWS_PER_BATCH [ = rows_per_batch ]
指定每一批处理数据的行数（即 rows_per_bacth）。当没有指定 BATCHSIZE 时使用，导致整个数据文件作为单个事务发送给服务器。服务器根据 rows_per_batch 优化大容量装载。默认情况下，ROWS_PER_BATCH 未知。
ROWTERMINATOR [ = 'row_terminator' ]
指定对于 char 和 widechar 数据文件要使用的行终止符。默认值是 /n（换行符）。
TABLOCK
指定对于大容量复制操作期间获取一个表级锁。如果表没有索引并且指定了 TABLOCK，则该表可以同时由多个客户端装载。默认情况下，锁定行为是由表选项 table lock on bulk load 决定的。只在大容量复制操作期间控制锁会减少表上的锁争夺，极大地提高性能。
注释
BULK INSERT 语句能在用户定义事务中执行。对于一个用 BULK INSERT 语句和 BATCHSIZE 子句将数据装载到使用多个批处理的表或视图中的用户定义事务来说，回滚它将回滚所有发送给 SQL Server 的批处理。
权限
只有 sysadmin 和 bulkadmin 固定服务器角色成员才能执行 BULK INSERT。
示例
本例从指定的数据文件中导入订单详细信息，该文件使用竖杠 (|) 字符作为字段终止符，使用 |/n 作为行终止符。
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:/orders/lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|/n'
)
本例指定 FIRE_TRIGGERS 参数。
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:/orders/lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = ':/n',
FIRE_TRIGGERS
)
=============================================================
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ] --BATCHSIZE指令来设置在单个事务中可以插入到表中的记录的数量
[ [ , ] CHECK_CONSTRAINTS ] --指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。若没有 CHECK_CONSTRAINTS 选项，则所有 CHECK 和 FOREIGN KEY 约束都将被忽略，并且在此操作之后表的约束将标记为不可信。
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] --指定该数据文件中数据的代码页
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] --指定 BULK INSERT 使用指定的数据文件类型值执行导入操作。
[ [ , ] 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' ] --指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。
)]
下面写个个简单的应用例子
bulk insert xsxt.dbo.tabletest from 'c:/data.txt'
with(
FIELDTERMINATOR=',',
ROWTERMINATOR='/n'
)
--------------------------------------------------------------------------------
--格式化文件生成部份:
--------------------------------------------------------------------------------
1.使用BCP导出整个表或视图。
BCP AdventureWorks.sales.currency out c:/currency1.txt -c -U"sa" -P"password" --使用密码连接
或
BCP AdventureWorks.sales.currency out c:/currency1.txt -c -T --使用信任连接
下面是上述命令执行后的输出结果
Starting copy...
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)
下面是currency1.txt的部分内容
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
... ... ...
... ... ...
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000
在使用密码登录时需要将-U后的用户名和-P后的密码加上双引号。
注：BCP除了可以在控制台执行外，还可以通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行BCP。如上述第一条命令可改写为
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:/currency1.txt -c -U"sa" -P"password"'
执行xp_cmdshell后，返回信息以表的形式输出。为了可以方便地在SQL中执行BCP，下面的命令都使用xp_cmdshell执行BCP命令。
2.对要导出的表进行过滤。
BCP不仅可以接受表名或视图名做为参数，也可以接受SQL做为参数。通过SQL语句可以对要导出的表进行过滤，然后导出过滤后的记录。
EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:/currency2.txt -c -U"sa" -P"password"'
BCP还可以通过简单地设置选项对导出的行进行限制。
EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:/currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'
这条命令使用了两个参数-F 10和-L 13，表示从SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记录进行导出。
3. 如何使用BCP导出格式文件
BCP不仅可以根据表、视图导入导出数据，还可以配合格式文件对导入导出数据进行限制。格式文件以纯文本文件形式存在，分为一般格式和xml格式。用户可以手工编写格式文件，也可以通过BCP命令根据表、视图自动生成格式文件。
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:/currency_format1.fmt -c -T'
上述命令将currency表的结构生成了一个格式文件currency_format1.fmt，下面是这个格式文件的内容。
9.0
3
1 SQLCHAR 0 6 "/t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "/t" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "/r/n" 3 ModifiedDate
这个格式文件记录了这个表的字段（共3个字段）类型、长度、字符和行分割符和字段名等信息。
BCP还可以通过-x选项生成xml格式的格式文件。
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:/currency_format2.fmt -x -c -T'
xml格式文件所描述的内容和普通格式文件所描述的内容完全一样，只是格式不同。
4. 如何使用BCP导入数据
BCP可以通过in命令将上面所导出的currency1.txt和currency2.txt再重新导入到数据库中，由于currency有主键，因此我们将复制一个和currency的结构完全一样的表。
SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency
将数据导入到currency1表中
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:/currency1.txt -c -T'
导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:/currency1.txt -c -F 10 -L 13 -T'
在导入数据时可以根据已经存在的格式文件将满足条件的记录导入到数据库中，不满足则不导入。如上述的格式文件中的第三个字段的字符长度是24，如果某个文本文件中的相应字段的长度超过24，则这条记录将不被导入到数据库中，其它满足条件的记录正常导入。
使用普通的格式文件
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:/currency1.txt -F 10 -L 13 -c -f c:/currency_format1.fmt -T'
使用xml格式的格式文件
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:/currency1.txt -F 10 -L 13 -c -x -f c:/currency_format2.fmt -T'
总结
BCP命令是SQL Server提供的一个快捷的数据导入导出工具。使用它不需要启动任何图形管理工具就能以高效的方式导入导出数据。当然，它也可以通过xp_cmdshell在SQL语句中执行，通过这种方式可以将其放到客户端程序中（如delphi、c#等）运行，这也是使客户端程序具有数据导入导出功能的方法之一。
--------------------------------------------------------------------------------
--实例部份：
--------------------------------------------------------------------------------
要把文本数据导入到数据库，本文只有3个字段，数据库表有7个字段，
怎么把文本字段的对应到表的字段，如何用bulk insert来实现?
数据库表
userinfo
id identity，userName，pass，address，phone，email，registerTime
文本格式是
userName，address，phone
hua，湖南，5971898
--SQL2005处理方式:
先在G盘存放一个格式化文件
G:/format.xml
- XML code :
-
<?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="," MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="/r/n" MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="userName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="address" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="phone" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
- SQL code :
-
G:/test.txt
userName,address,phone
hua,湖南,5971898
--SQL SERVER
--创建表
CREATE TABLE userinfo(id INT identity,userName varchar(20),
pass varchar(20),address varchar(100),phone varchar(20),
email varchar(128),registerTime datetime)
--导入
INSERT INTO userinfo(userName,address,phone)
SELECT * FROM OPENROWSET(BULK 'G:/test.txt',FORMATFILE='G:/format.xml',FIRSTROW=2) AS T;
--查看数据
SELECT * FROM userinfo;
/*
id userName pass address phone email registerTime
----------- --------- ---------- ----------- ---------- ---------- -------------
1 hua NULL 湖南 5971898 NULL NULL
(1 行受影响)
*/
--SQL2000处理方式:
先在G盘存放一个格式化文件
G:/format.fmt
- FMT code :
-
8.0
3
1 SQLCHAR 0 20 "," 2 userName Chinese_PRC_CI_AS
2 SQLCHAR 0 100 "," 4 address Chinese_PRC_CI_AS
3 SQLCHAR 0 20 "/r/n" 5 phone ""
- SQL code
-
G:/test.txt
userName,address,phone
hua,湖南,5971898
--SQL SERVER
--建表
CREATE TABLE userinfo(id int identity,userName varchar(20),
pass varchar(20),address varchar(100),phone varchar(20),
email varchar(128),registerTime datetime)
--导入
BULK INSERT userinfo
FROM 'G:/test.txt'
WITH
(
FORMATFILE = 'G:/format.fmt',
FIRSTROW = 2
)
--查看数据
SELECT * FROM userinfo;
/*
id userName pass address phone email registerTime
----------- --------- ---------- ----------- ---------- ---------- -------------
1 hua NULL 湖南 5971898 NULL NULL
(1 行受影响)
*/-------------------------------------------------------------------------------------------------
8.0 --这个8,表示版本是8.0
3 --这个3.表示数据文件中有几列.
第一列的1,2,3表示数据文件的列的序号
而后面的第六列的2,4,5表示userName,Address,phone在表中.是第几列
1 SQLCHAR 0 20 "," 2 userName Chinese_PRC_CI_AS
2 SQLCHAR 0 100 "," 4 address Chinese_PRC_CI_AS
3 SQLCHAR 0 20 "/r/n" 5 phone "" -
--------------------------------------------------------------------------------------------------
原贴地址:
http://topic.csdn.net/u/20090913/15/fa2e7e65-73d8-4b64-b6e0-bd583f564d86.html?95717
------------------------------------------------------------------------------------------
1、有一固定长度的文本，如下:
a.txt
aaaaaaabbbbbbbcccccccc01234567890
aaaaaaabbbbbbbcccccccc01234567890
aaaaaaabbbbbbbcccccccc01234567890
aaaaaaabbbbbbbcccccccc01234567890
aaaaaaabbbbbbbcccccccc01234567890
共5个字段，
aaaaaaa
bbbbbbb
cccccccc
0123456789
0
如何批量导入a.txt进数据库，要存储过程或语句。
我看了一下，好像bulk不行，高手请教教我，谢谢！
2、如何能快速的把10w级以上的文本数据导入数据库，最好控制到1-2分钟内。
------------------格式化文件: tb.xml<?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="CharFixed" LENGTH="4" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="4" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="4" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="/r/n" MAX_LENGTH="10" COLLATION="Chinese_PRC_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="col1" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="col2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="col3" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="col4" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="col5" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>数据文件: data.txt
abcd1234efgh5678ijkl
ejaijfefajhjahduuehf
euqrvclanqa4512arfqe
创建表,并使用BULK INSERT导入表
-
CREATE TABLE tb(col1 varchar(10),col2 varchar(10),
col3 varchar(10),col4 varchar(10),
col5 varchar(10));
BULK INSERT tb
FROM 'g:/data.txt'
WITH
(
FORMATFILE='g:/tb.xml'
)
查看表数据:
-
SELECT * FROM tb;
/*
col1 col2 col3 col4 col5
---------- ---------- ---------- ---------- ----------
abcd 1234 efgh 5678 ijkl
ejai jfef ajhj ahdu uehf
euqr vcla nqa4 512a rfqe
(3 行受影响)
*/