2.数据加载

　　SQL Server的大容量文本数据文件导入主要有2种方式，第一种在操作系统下，用bcp命令导入，这个命令的优点是支持各种文件格式，功能强大，缺点是比较复杂。第二种是在SQL Server交互界面中用Bulk insert语句导入，这个语句的语法简单，但支持的文件种类不如bcp多。两种方式都可以有效地减少日志的产生，达到高速导入的目的。

　　下面分别举例说明这2种方式的导入。

　　第1种，bulk insert 导入语句的语法结构是：

BULK   INSERT   表名    FROM   '文本文件全路径' WITH
(
FIELDTERMINATOR   =   '列（字段）分隔符',
ROWTERMINATOR   =   '行分隔符'
)

 　　Tpc-h的数据文件的列分隔符是竖线，包括最后一个数据列，行分隔符是回车换行，在Windows操作系统中用'\n'表示，因此，我们用下面的语句：

2> BULK   INSERT   dbo.[supplier]       FROM   'd:\soft\supplier.tbl' WITH
3> ( FIELDTERMINATOR   =   '|',
4> ROWTERMINATOR   =   '|\n'
5> )
6> go
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 15 ms.

SQL Server Execution Times:
CPU time = 1266 ms,  elapsed time = 1328 ms.
(100000 rows affected)

 　　10万行的导入时间大约是1.3秒。

　　第2种，bcp命令的命令行格式是：

F:\soft\1>bcp /?
用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]       [-e 错误文件]
  [-F 首行]                   [-L 末行]             [-b 批大小]
  [-n 本机类型]               [-c 字符类型]         [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
  [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]               [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]             [-U 用户名]           [-P 密码]
  [-T 可信连接]               [-v 版本]             [-R 允许使用区域设置]
  [-k 保留 Null 值]           [-E 保留标识值]
  [-h"加载提示"]              [-x 生成 xml 格式化文件]
  [-d 数据库名称]

 　　对于我们的数据，如果像bulk insert命令一样只限制分隔符，bcp认为信息不够，需要人工确认每一列的格式，我们如果采用默认值是无法导入成功的。

D:\soft>bcp customer in customer.tbl -Usa -Pabcd1234  -t"|" -r"|\n"

Enter the file storage type of field C_CUSTKEY [int]:
Enter prefix-length of field C_CUSTKEY [0]:
Enter field terminator [|]:

Enter the file storage type of field C_NAME [char]:
Enter prefix-length of field C_NAME [2]:
Enter field terminator [|]:

。。。

Enter the file storage type of field C_ACCTBAL [decimal]:
Enter prefix-length of field C_ACCTBAL [1]:
Enter field terminator [|]:

。。。

Enter the file storage type of field C_COMMENT [char]:
Enter prefix-length of field C_COMMENT [2]:
Enter field terminator [|
]:

Do you want to save this format information in a file? [Y/n] Y
Host filename [bcp.fmt]: customer.fmt

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]String data, right truncation
。。。

 　　这是因为，bcp命令如果没有指定-c参数，那么把外部数据文件视为二进制格式，这样int类型是4个字节，date类型是7个字节等。这时即使把前缀长度统统改为0，虽然能够导入，数据也不正确。比如，外部文件中的0，导入数据库中就变成了48，刚好是字符'0'的ASCII码。要正确导入，我们需要手工修改刚才产生的fmt文件，将所有列都改为SQL_CHAR类型，如下文本所示。再用-f指定格式文件就可以了。而使用-c参数，就没有这么麻烦，只要指定分隔符即可。推荐使用-c方式导入。

D:\soft> type supplier.fmt
10.5
7
1 SQLCHAR 0 14 "|" 1 S_SUPPKEY ""
2 SQLCHAR 0 25 "|" 2 S_NAME Chinese_PRC_CI_AS
3 SQLCHAR 0 40 "|" 3 S_ADDRESS Chinese_PRC_CI_AS
4 SQLCHAR 0 14 "|" 4 S_NATIONKEY ""
5 SQLCHAR 0 15 "|" 5 S_PHONE Chinese_PRC_CI_AS
6 SQLCHAR 0 19 "|" 6 S_ACCTBAL ""
7 SQLCHAR 0 101 "|\r\n" 7 S_COMMENT Chinese_PRC_CI_AS

D:\soft> bcp supplier in supplier.tbl -Usa -Pabcd1234 -f supplier.fmt

100000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 2469   Average : (40502.23 rows per sec.)

--把array size改大就快了,同时限制每批提交的数据行数
D:\soft> bcp supplier in supplier.tbl -Usa -Pabcd1234 -f supplier.fmt -a 40960 -b 1000000


100000 rows copied.
Network packet size (bytes): 32576
Clock Time (ms.) Total     : 843    Average : (118623.96 rows per sec.)

--使用-c参数，指定文件类型是文本格式
D:\soft>bcp part in part.tbl -Usa -Pabcd1234 -c -t"|" -r"|\n" -a 40960 -b 2000000

Starting copy...
2000000 rows sent to SQL Server. Total sent: 2000000

2000000 rows copied.
Network packet size (bytes): 32576
Clock Time (ms.) Total     : 20250  Average : (98765.43 rows per sec.)

--使用可信连接，并指定数据库为tpch,输出导入日志文件
bcp part in part.tbl -d tpch -T -c -t"|" -r"|\n" -a 40960 -b 2000000 -o F:\DF\bcpp.log

 　　我们看到使用系统默认的数组大小，bcp导入的速度比bulk insert 要慢一些，10万行的导入时间大约是2.5秒，但是指定一个较大的数组尺寸比如32K，导入速度就比bulk insert快了，10万行只要843毫秒。其他报表的导入情况类似。-b参数可以限制每批提交的行数，避免提交太频繁影响性能。

　　8个表bcp导入的时间分别是13秒、685秒、129秒、67秒、19秒、1秒、0秒、0秒，总时间914秒。

　　SQL Server 允许多个客户端“并行地大容量导入数据”到单个无索引的表中。这样可以提高大容量导入操作的性能。但必须要求存在多个外部文件，再人工地发起多个不同的命令，无法在单独一个命令中使用单独一个外部文件并行导入。