第5章 BCP和其他基本的大容量操作
你的系统将运行在虚幻的世界里，那么或许可以跳过本章。不幸的是，现实世界不会是那样的，因此，可能要在这里逗留一阵子了。
有些时候，需要移动大批的数据。你需要引入格式不正确的数据，或者引入位于另一个应用程序的数据文件中的数据。有时，现实就是这样充满阻碍的。幸好SQL Server有两个工具能帮助你快速地移动数据——大容量复制程序（BCP）和SQL Server Integration Services（SSIS）。本章中，我们将主要讨论第一个。此外，还将讨论BCP的近亲——BULK INSERT命令和OPENROWSET (BULK)。在下一章中，我们将探究SSIS。
BCP是一位老朋友。你肯定认识这位老朋友，你现在很少看见他们，但是，当你们碰面时，会回忆起过去一起经历的种种事情。很长一段时间以来，它都是我们移动大批数据的方法——并且，它做起事情来快得令人惊叹（就它所能达到的程度来说现在依然是这样）。然而，它缺乏吸引力——坦白地说，从7.0版开始，它在大多数领域都没多少吸引力了。
那么，为什么我甚至要花上一章来讲述它？这是由于BCP依然有着不容置疑的用处。它的优点有：
l 非常简洁；
l 能够极快地移动大量的数据；
l 它是遗留的事物，即可能有已经运行着的代码有效地利用了它，因此没有理由要改变它；
l 使用神秘却很传统的脚本风格（这对于一些人来说，或许很有吸引力）；
l 始终如一。
BCP用来把文本和SQL Server本机格式数据传入和传出SQL Server表。在最近的几个版本中，BCP几乎没什么改变，并且，尽管其他大容量功能不断削弱BCP的有用性，但它依然还是有其作用的。可以把BCP想成是数据泵，除了尽可能高效地把数据从一个地方移动至另一个地方外，几乎没有其他的功能。在本章中将谈到其他几种大容量操作，这些操作常常更容易使用，但是这常常是以降低灵活性为代价的。
本章中，我们将详细讨论BCP的一些细节，然后，将以我们在BCP上学到的知识为基础，来了解其他实现类似目的的多种功能——尽可能快地把数据导入导出系统。
5.1 BCP实用工具
BCP从操作系统命令行提示符运行，以导入或导出本机数据（特定于SQL Server）、ASCII文本或Unicode文本。这意味着能够从操作系统批文件、用户定义的存储过程以及其他的一些地方来执行BCP。BCP还能作为计划作业的一部分来运行，或者通过使用shell命令从.NET对象中执行。
像大多数命令行工具一样，可以使用连字号（-）或斜线（/）来指定选项。然而，与大多数DOS或Windows类型的工具不同，选项开关是区分大小写的。
5.1.1 BCP语法
唉！要了解的语法真多，因此，让我们逐一讲述这些开关（谢天谢地，由于这里的大多数开关都是可选的，你通常只需包含进一小部分）。
注意，用于BCP实用工具的许多开关都是区分大小写——通常，一个指定的字母，在其大小写之间，有着完全不同的含义。
|
参 数 |
说 明 |
|
Database name |
完全与其名称的意思一样。基本上，这是四部分命名方案的标准部分。如果不指定，则使用用户的默认数据库 |
|
owner |
四部分命名方案的一部分。同样，与其名称的意思一样 |
|
Table或View name "query" |
只能是其中一个——表、视图或查询 这是输入的目标表或视图，或者输出的源表或视图 SQL Server查询只能用作BCP输出的源，并且，只能在指定了queryout时使用。如果查询返回多个结果集，则BCP只使用第一个结果集 |
|
in data file out data file queryout data file format data file |
同样，只能是其中一个。如果使用了其中的任何一个，还必须给出源或目标文件 确定BCP操作的方向。in表明把数据从源文件导入到表或视图中；out表明把数据从表或视图导出到目标文件中；只有在使用查询作为输出的源、把数据输出到目标文件中时，才使用queryout；使用format根据指定的格式选项创建格式化文件。你还必须指定-f，并指定格式选项（-n、-c、-w、-6、-C或-N），或者对BCP交互的提示作答 源或目标的路径和文件名以<data file>来指定，并且，包含的字符不能超过255个 |
|
-m <maximum errors> |
可以指定在SQL Server取消大容量复制操作之前，允许的错误的最大数目，默认是10个错误。BCP把无法复制的每一行都计为一个错误 |
|
-f <format file> |
格式化文件中包含的是存储的响应，这些响应来自同一个表或视图上以前的BCP操作。该参数应当包含格式化文件的完整路径和文件名。这一选项主要与in和format选项一起使用，以便在使用或创建格式化文件时，指定路径和文件名 |
|
-x |
用于生成基于XML的格式化文件，而不是默认的文本形式的文件（虽然非XML形式的文件是遗留的支持，但是，它现在依然是默认的文件形式）。该选项必须与format和-f选项一起使用 |
（续）
|
参 数 |
说 明 |
|
-e <error file> |
可以指定错误文件的完整路径和文件名，以便存储BCP无法传输的所有行。否则，不会创建错误文件。任何错误消息将显示在用户的工作站上 |
|
-F first row |
如果想要指定大容量复制操作要复制的第一行，可以使用该选项。如果不指定该选项，则BCP默认为1，并开始复制源数据文件中的第一行。当想要大块地装载时，使用该选项会非常便利，并且，使用该选项，能够在前面的装载停止的地方从头拣选 |
|
-L last row |
该选项是对-F的补充。它用来决定作为BCP执行的一部分，想要装载的最后一行。如果不指定，则BCP默认为0，即源文件中的最后一行。当与-F一起使用时，该选项允许一次装载一大块数据——装载小块的数据，然后，在上一次装载停止的地方开始下一次的拣选 |
|
-b batch size |
可以指定作为一批复制的行数。一个批作为一个单独的事务进行复制。与所有的事务一样，一个批中的行以“要么全有、要么全无”的方式提交——要么所有的行都被提交，要么事务被回滚，就好像整个批处理都不曾发生一样。-h（提示）开关有类似的选项（ROWS_PER_BATCH），该选项被认为是与-b互斥（两者都不使用或使用其中一个，但不会一起使用二者） |
|
-n |
使用本机数据类型（SQL Server数据类型）进行复制操作。使用该选项可以避免必须回答关于传输中要用到的数据类型的问题（它只是使用本机类型） |
|
-c |
该选项指定对所有的字段使用字符数据（文本）执行操作，因此不需要每一个字段是单独的数据类型。如果没有使用-t选项，则使用制表符作为默认的字段分隔符，并且，如果不使用-r来指定行终止符，则使用换行符作为行分隔符 |
|
-w |
-w选项与-c选项类似，只是-w为所有字段使用Unicode数据类型而不是ASCII。同样，如果没有用-t和-r选项进行覆盖，则分别以制表符和换行符作为字段分隔符和行分隔符。该选项不能在SQL Server 6.5或更早的版本中使用 |
|
-N |
该选项与-w基本相同——对于字符数据使用Unicode字符，而对于非字符数据使用本机数据类型（数据库数据类型）。当把数据从一个SQL Server传送到另一个SQL Server时，该选项能提供更高的性能。与使用-w一样，该选项不能在SQL Server 6.5或更早的版本中使用 |
|
-V(60/65/70/80) |
让BCP使用SQL Server早期版本中可用的数据类型格式：60使用6.0数据类型，65使用6.5数据类型，70使用7.0数据类型，80使用2000数据类型。该选项替代-6选项 |
|
-6 |
用该选项来强迫BCP使用SQL Server 6.0或6.5数据类型。该选项与-c或-n结合使用仅仅为了向后兼容——只要可能，应当使用-V |
|
-q |
使用-q指定表或视图名称包含非ANSI字符。实际上，该选项为BCP使用的连接执行SET QUOTED_IDENTIFIERS ON语句。完全限定名称、数据库、所有者和表或视图必须用双引号括起来，就像这样“database name.owner.table” |
|
-C <code page> |
该选项用来指定数据文件中数据的代码页。该选项仅在char、varchar或text数据包含小于32或大于127的ASCII字符值时才适用。值为ACP的代码页指定ANSI/Microsoft Windows(ISO 1252)。OEM指定客户端默认代码页。如果指定RAW，则不会发生代码页转换。此外，还可以选择提供特定的代码页值。要尽可能避免使用该选项——而是在格式化文件中或当被BCP问及时，使用指定的排序规则 |
|
-t <field terminator> |
该选项允许你覆盖默认的字段终止符。默认的终止符是制表符。可以指定的终止符如：制表符（\t）、换行符（\n）、回车符（\r）、反斜杠（\\）、空终止符（\0）、任何可打印的字符或最长可达10个可打印字符的字符串。例如，为逗号分隔的文本文件使用-t选项 |
（续）
|
参 数 |
说 明 |
|
-r <row terminator> |
该选项与-t选项类似，除了这里允许覆盖的是默认的行终止符（而非字段终止符）。默认的终止符是换行符\n。另外，规则与-t相同 |
|
-i <input file> |
你可以选择以input file来指定响应文件，该文件包含在交互模式下执行BCP操作时要使用的响应（这样就不必回答大量的问题） |
|
-o <output file> |
可以把BCP输出从命令提示行重定向到输出文件。当从无人参与的批处理或存储过程执行BCP时，该选项提供了捕获命令输出和结果的方法 |
|
-a <packet size> |
可以使用该选项覆盖在网络上传输的数据包的默认大小。当有良好的网络质量（几乎没有CRC错误）时，较大的数据包会带来较好的性能。指定的值必须在4096～65535，4096和65535也包含在内，并且，该值将覆盖为服务器设置的默认值。安装时，默认的包的大小是4096字节。可以使用SQL Server Management Studio或sp_configure系统存储过程来替代该值 |
|
-S <server name> |
如果从服务器上运行BCP，默认的服务器是本地的SQL Server。使用该选项可以指定一个不同的服务器，并且，在网络环境中，当从远程系统运行BCP时，必须使用该选项 |
|
-U <login name> |
如果不是通过可信连接与SQL Server进行连接，则必须提供用于登录的有效用户名 |
|
-P password |
当你提供了用户名时，还必须提供密码。否则，将提示你输入密码。如果把-P作为最后一个选项，并且没有提供密码，则指定密码为null |
|
-T |
可以选择通过可信连接使用网络用户凭据连接到服务器。如果指定了可信连接，则不需要为连接提供登录名或密码 |
|
-v |
当使用了该选项时，BCP将返回版本号和版权信息 |
|
-R |
通过该选项指定，当进行货币、日期和时间数据的复制时，使用客户端区域设置中定义的区域格式。默认情况下，忽略区域设置 |
|
-k |
在大容量复制过程中，使用该选项将覆盖列的默认值，忽略任何默认约束。空列将保留空值，而不是列的默认值 |
|
-E |
当导入的源文件包含标识列值时使用该选项，该选项本质上等同于SET IDENTITY_INSERT ON。如果不指定，则SQL Server将忽略源文件中提供的值，并自动生成标识列值。当从源文件中导入的数据不包含标识值时，可以使用格式化文件来忽略标识列，并让SQL Server产生标识值 |
|
-h “hint[,…]” |
你可以通过提示选项，指定大容量操作使用的一个或多个提示。SQL Server 6.5或更早的版本不支持-h选项 |
|
ORDER column [ASC|DESC] |
当源数据文件的排序次序与目标表中的聚集索引匹配时，可以使用该提示提高性能。如果目标表没有聚集索引，或者数据按不同的次序排序，则将忽略ORDER提示 |
|
ROW_PER_BATCH=nn |
该提示可以代替-b选项，指定作为一批传送的行数。不要将该提示与-b选项一起使用 |
|
KILOBYTES_PER_BATCH=nn |
对于要在一个批中传送的数据，可以选择以千字节的近似值来指定批的大小 |
|
TABLOCK |
该选项将导致操作期间获得表级别的锁。默认情况下，锁定行为由表选项table lock on bulk load设置 |
|
CHECK_CONSTRAINTS |
默认情况下，在导入操作期间将忽略CHECK约束。使用该提示强制导入期间检查CHECK约束 |
|
FIRE_TRIGGERS |
在SQL Server 2000中添加进来，与CHECK_CONSTRAINTS类似，该选项使得目标表上的所有触发器在事务执行期间激发。默认情况下，触发器在大容量操作期间不会激发。SQL Server 2000之前的版本不支持该选项 |
BCP在交互模式中运行，执行命令时，如果没有指定-f、-c、-n、-w、-6或-N，则会提示格式信息。当在交互模式中运行时，BCP在收到格式信息后，还会提示创建格式化文件。
5.1.2 BCP导入
到现在为止，我们一直在讲述预备知识。接下来，可以开始讨论BCP具体的事情了。
或许，BCP最常见的用途是把大量的数据导入到现有的SQL Server表和视图中。要导入数据，必须具有对服务器的访问权限（或者通过登录ID获得，或者通过可信的连接获得），并且，必须在目标表或视图上具有INSERT和SELECT许可。
源文件可以包含本机代码、ASCII字符、Unicode，或混合的本机和Unicode数据。要记得使用合适的选项描述源数据。此外，为了让数据文件可用，必须说明字段终止符和行终止符（使用-t和-r），或者，分别用默认的制表符和换行符来终止字段和行。
在开始前，一定要了解将到达的目的地。BCP有一些怪异的行为，这些行为能够影响数据的导入。为timestamp列或计算列提供的值将被忽略。如果源数据文件中有这些列的值，它们将被忽略。如果源数据文件不包含这些列的值，则需要使用格式化文件（将在本章的后面看到），并忽略这些列。
在可以使用的所有软件中，这是你不时会碰到的奇异的行为之一。在这种情况下，如果目标表中包含这些列，那么，即使SQL Server终将忽略这些数据，也必须包含代表timestamp或计算数据的列——很傻吧？此外，可以使用格式化文件明确地表明要忽略讨论中的这些列，以此来解决该问题。
对于BCP操作而言，规则将被忽略。如果不指定FIRE_TRIGGERS和（或）CHECK_CONSTRAINTS提示，所有的触发器和约束都将被忽略。始终强制使用Unique约束、索引、以及主键（或外键）约束。如果不指定-k选项，将强制使用默认约束。
1．数据导入的示例
要了解BCP导入是如何工作的，最简单的方法是看一个例子。我们从简单的例子入手，一个以制表符分隔的文件，该文件包含AdventureWorks数据库的货主信息。数据如下所示：
要在本地服务器上使用可信的连接把这些数据导入到Department表，运行：
这里有两件重要的事情：第一，迄今为止，我们运行过的所有事情都是在Management Studio中完成的。然而，对于BCP来说，需要在命令提示符框中输入命令。第二，需要修改前面的命令行，以便匹配你下载的本书的示例文件（或数据）。
由于在Department表中，第一列是标识列，并且没有指定-E选项，因此，SQL Server将忽略文件中的标识值，并产生新的值。-c选项将源数据确定为字符数据，而-T指定使用可信的连接。
注意，如果没有使用Windows身份验证，并且，没有在SQL Server中为网络登录设置适当的权限，那么，你可能需要对上例进行修改以使用-S和-P选项。
当开始执行时，SQL Server很快会告知我们关于大容量操作进行情况的一些基本信息：
我们可以回到Management Studio，并证实数据正如预期的那样进入到了Department表中：
我们重新找回几行，最重要的是要看看期望以BCP操作中导入的那两行：
像往常一样，要注意，除了我们刚刚导入的两行之外，你的数据看起来有些不同，这取决于你在本书的哪些部分运行过示例，哪些部分没有运行过示例，以及你在已完成的事情上的操作次数。对于本例而言，我们只是想看到Smart Guys和Product Test以适当的信息复制到了表中——标识值将根据特定服务器上的下一个值来重新分配。
下面来看一个更复杂的示例。假设有一个名为CustomerList的表。表CustomerList的CREATE语句如下所示：
我们有一个以逗号分隔的文件（其格式与.CSV文件一样），该文件中包含新顾客的信息。这一次，文件看起来像下面这样：
为什么源数据文件中都是些逗号呢？这些逗号是CustomerList表中列的占位符。源文件没有为所有的列提供值，因此，使用逗号来忽略这些列。对于源文件没有为所有的列提供值一事，处理方法并非只有这一种。你可以使用格式化文件，把源文件与目的位置进行匹配。在本章中，稍后将讨论格式化文件。
想象一下，运行BCP把数据导入到远程系统中。命令如下所示：
这里显示的换行是为了让命令行更容易阅读。在你自己试验该例时，请不要输入回车。把这些命令像单独的一行那样输入，让它在命令提示符中自行换行即可。
再次将数据确定为字符数据。-t选项将文件确定为以逗号分隔（终止）的数据，-r\n选项把换行符定义为行终止符。另外，这里提供的服务器连接信息也略有些变化，以sa作为登录名，以bubbagump为密码。
同样，BCP确认了传输，同时给出基本的状态：
并且，我们将再次验证数据已经如期望的那样到达了其应该在的地方：
毫无疑问，我们的数据都在那里……
2．记录日志与不记录日志
BCP既能以快速模式（不记录日志）运行，也能以慢速模式（记录日志）运行。每一种方式都有其优点。快速模式能让你获得最好的性能，但慢速模式能提供最大限度的可恢复性。由于慢速模式要记录日志，因此，可以在导入之后，立即进行一次快速事务日志备份，这样就能够在出现故障时恢复数据库。
当需要传输大量的数据时，快速模式常常是最好的选择。快速模式不仅传输速度更快，而且，由于操作不记录日志，因此不必担心事务日志耗尽空间。这里的要求是什么呢？如果要以不记录日志的方式运行BCP，必须满足几个条件：
l 不能复制目标表；
l 如果目标表有索引，则该表当前必须没有任何行；
l 如果目标表中已经有行存在，则该表上必须没有索引；
l 指定了TABLOCK提示；
l 目标表上必须没有触发器；
l 对于早于SQL Server 2000的版本，必须把select into/bulkcopy选项设置为true。
显然，如果想要在拥有数据的索引表中进行快速模式复制，必须：
l 删除索引；
l 删除任何触发器；
l 运行BCP；
l 对目标表重建索引；
l 重新创建所有的触发器。
完成了不记录日志的BCP操作后，必须立即对目标数据库进行备份。
如果目标表不满足进行快速BCP的条件，则操作将被记录在事务日志中。这意味着在传输大量的数据时，可能要冒填满事务日志的风险。可以使用WITH TRUNCATE_ONLY选项运行BACKUP LOG，从而清除事务日志。TRUNCATE_ONLY选项不备份任何数据，只删除日志中不活动的部分。
BCP操作对于事务日志的大小可能是致命的，这一点我无论强调多少次也不过分。如果不能实现以最小方式记录操作，那么，需要考虑把批的大小调整得小一些，并且，考虑在操作的期间打开TRUNCATE ON CHECKPOINT。另一个解决方案是使用-F和-L选项，以便同时把事情作为一个块来处理，并在每块数据之间截断日志。
5.1.3 BCP导出
如果你将通过大容量操作接受数据，那么，你也可能想要把数据提取出来。
BCP允许从表、视图或查询中导出数据。你必须指定一个目的文件名——如果文件已经存在，则它将被覆盖。与导入操作不同，在导出时，不允许忽略列。timestamp、rowguid以及计算列与任何其他SQL Server列一样，以同样的方式导出（就好像它们是“真正的”数据一样）。要执行导出，必须在源表上具有适当的SELECT权限。
来看两个使用AdventureWorks数据库中的HumanResources.Department表的简单例子。
要使用默认的格式把数据导出到数据文件，可以运行：
运行后，将创建如下的文件：
在这里，我们不必使用格式化文件，也不会就字段长度或类似的信息对我们进行提示——选项-c表明，只希望所有的事情（无论什么类型）都以默认的格式按基本的ASCII文本那样导出。默认情况下，以制表符作为字段分隔符，而以换行符作为行分隔符。
要记住，如果目的文件已经存在，则它将被覆盖——这将在没有任何类型的提示或警告的情况下发生。
如果要把分隔符改为某种特定的字符，可以运行如下代码：
注意最后的逗号——这不是打字错误。位于t后面的字符是字段分隔符——这里是逗号。
运行上述语句，将得到如下的结果：
我们使用逗号分隔符替代制表符，这样得到了算是.CSV的文件。