24. 加载数据库加载操作的效能 大量复制工具程序BULK INSERT 陈述式数据转换服务Staging 资料表SELECT...INTO 陈述式本章总结在学会了建立数据库和数据库数据表，就可学习加载数据了。将数据加载数据库的方法有好几种，采用何种方法取决于数据来源、对数据进行何种处理，以及资料将加载何处。本章将学习以下几种加载数据库的方法：• 使用大量复制工具程序（Bulk Copy Program，BCP） ：BCP 是由 Microsoft SQL Server 2000 提供的外部程序，便于加载数据文件至数据库，BCP 也可以从 SQL Server 中的数据表复制数据至数据文件中。 • 使用 BULK INSERT 指令 ：BULK INSERT T-SQL 指令可从数据文件复制大量数据至 SQL Server 数据表中。由于此指令是 SQL 陈述式（在 ISQL、OSQL 或 Query Analyzer 中执行），处理过程将当成 SQL Server 的执行绪执行。该指令不能从 SQL Server 复制数据到数据文件中。 • 使用数据转换服务（Data Transformation Services，DTS） ：DTS 是一组由 SQL Server 提供的工具组，便于复制数据到 SQL Server，或从 SQL Server 复制出数据。DTS 包括汇出和汇入数据精灵。 ________________________________________说明 尽管 Staging 数据表并没有提供加载数据的方法，但一般用于数据库的加载。________________________________________每种方法都有其特点和性质，以适合使用者不同的加载需求。________________________________________说明 从备份文件复原数据库也可以视为数据库加载的一种形式，这点将在 第三十二章 和 第三十三章 进行讨论。________________________________________BCP 或 BULK INSERT 陈述式的某些数据库参数设定是共通的，且数据库参数定义了 BCP 如何执行，因此必须在执行加载操作之前完成参数的设定。下列的方法也很实用： • SELECT...INTO 陈述式 ：用于将数据复制至另一个数据表。 • Staging 资料表 ：为暂存数据表，通常用于在数据库中转换数据，使数据加载过程更顺利，并可于加载过程中修改数据。 加载操作的效能 本段会介绍三种常用于加强加载操作效能的设定选项。其中两种选项会在执行大量复制操作时影响交易记录文件，另一种则影响锁定。使用 大量复制 的方法一次复制大量数据，可视为重建一份数据最有效率的方法。交易记录文件选项 SQL Server 使用复杂的交易记录文件机制来确定数据不会因为系统故障而遗失。交易记录文件对于系统中数据的完整性是必要的，但可能会大量的增加系统负担。透过减少大量加载数据时的交易数据记录量，可降低系统的负担。________________________________________说明 发生系统故障后，SQL Server 会复原数据库。发生故障时还未被认可的交易都会被 复原（Roll Back） （取消操作）；发生故障时已经认可的交易都会 向前复原（Roll Forward） 。复原或向前复原可以将系统复原到故障发生前的状态。备份和复原将在 第三十二章 和 第三十三章 讨论。________________________________________在预设状态下，所有的数据库插入操作都会被完全的记录下来，以便在系统故障时可向前复原或复原插入的数据。藉由停用大量数据复制时的完全记录（使用BCP、BULK INSERT 陈述式或 SELECT...INTO 陈述式），您可以减少记录的数据量，但也使系统只允许复原操作（认可的交易就消失了）。这个选项可使大量复制效能最佳化，但是由于用于向前复原的记录未被记录，因此当系统发生问题时，就必须重新开始数据库加载。当满足下列状况时，大量加载操作的完全记录就会停用： • 数据库选项 SELECT INTO/BULKCOPY 被设为 TRUE ，下列为使用sp_dboption预存程序的语法： exec sp_dboption database_name, "select into/bulkcopy", TRUE• 使用 Enterprise Manager 也可以设定该选项， 第八章 有 Enterprise Manager 的详细介绍。 • 加载数据的目标数据表未被设定为复写（复写在 第二十六章 、 第二十七章 及 第二十八章 有介绍）。 • 已经指定 TABLOCK 提示（关于提示的相关信息可参照本章 〈选择性参数〉 一节）。如果被加载的数据表已定义了索引，就不需要指定 TABLOCK 提示。 另外，当trunc.log on chkpt数据库选项设为 TRUE 时，会停止交易记录文件的记录。这也可改善大量复制的效能，但也代表当系统发生故障时，就无法执行向前复原或复原。________________________________________注意 您应该只在最初建立数据库，并且有将大量数据加载至数据库的需求时才将trunc.log on chkpt设为 TRUE，停止交易记录文件的记录会影响整个数据库，并会导致系统在发生问题时无法复原。因此在正常操作的情况下，如果「复原」这个项目对系统很重要，就不应该使用这个选项。如果已经将trunc.log on chkpt设为 TRUE，在完成加载操作后要记得将选项在设定为 FALSE。________________________________________trunc.log on chkpt可用sp_dboption设定，语法如下：exec sp_dboptiondatabase_name, "trunc. log on chkpt", TRUE________________________________________说明 您可以在数据库 属性 窗口中的 选项 卷标页设定更多的选项，如图 24-1 所示。 限制存取 可以限制对特定角色或单一使用者存取数据库； 只读 设定不允许写入数据库。 预设为ANSI NULL 指定在预设状态下，数据库的数据行是否定义为 NULL 或 NOT NULL； 重复触发程序 让触发程序可以递归的触发； 自动更新统计资料 可让 SQL Server 在查询最佳化过程自动重建过期的统计资料； 损毁页侦测 可侦测到不完全的分页； 自动关闭 指定数据库将在其资源被释出并且所有使用者离开之后关闭； 自动压缩 指定 SQL Server 将会周期性的压缩数据库档案； 自动产生统计资料 指定查询最佳化所需的任何遗失统计数据将在最佳化过程中自动建立； 使用引号识别项 选取这个选项指定双引号只能用于识别项，例如数据行与数据表名称，而字符字符串必须括在单引号中。________________________________________锁定选项 使用table lock on bulk load选项还可改善大量复制的效能，在大量复制运作模式下，这个选项以单一数据表锁定取代多个数据列锁定。使用sp_tableoption来设定table lock on bulk load，参数的设定如下：exec sp_tableoption "table_name", "table lock on bulk load", TRUE加载完成后，记得要重新设定trunc.log on chkpt的参数。由于table lock on bulk load选项只有在大量复制时，才会影响数据表的锁定模式，所以当您没有执行大量复制时，不会降低执行效能。
图24-1 数据库「属性」窗口的「选项」卷标页________________________________________说明 要取得table lock on bulk load选项的执行效能，您必须使用 TABLOCK 设定。________________________________________大量复制工具程序 使用 BCP，可从一个数据文件中复制数据到 SQL Server，或是从 SQL Server 复制数据到数据文件中。若要将数据从其它数据库传送到 SQL Server，BCP 也很实用。本节会学习如何使用 BCP 及其选项，并学习如何将数据格式化，以便使用 BCP 将数据汇入 SQL Server 或从 SQL Server 汇出。BCP 语法 BCP 是指令行的执行程序，使用 BCP 需要特定的参数，而 BCP 提供许多选择性的参数供您使用。 BCP 指令的格式如下：bcp {[[database_name.][owner].]{table_name|view_name} | "query"} {in | out | queryout | format}data_file [-mmax_errors] [-fformat_file] [-eerror_file] [-Ffirst_row] [-Llast_row] [-bbatch_size] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6] [-q] [-Ccode_page] [-tfield_term] [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size] [-Sserver_name[/instance_name]] [-Ulogin_id] [-Ppassword] [-T] [-v] [-R] [-k] [-E] [-h "hint[,...n]"]必要参数 必要参数指定数据的撷取和插入的位置。如之前所述，使用 BCP 可以从数据文件撷取和插入数据到一个 SQL Server 数据表或检视表中，或是从一个数据表（或检视表）撷取和插入资料到数据文件中。在大量复制操作中所使用的数据表或检视表，可用以下两种方式指定：第一种方法为使用table/view_definition参数，最简单的定义即为数据表或检视表的名称。如之前示范过的指令，可以指定数据表或检视表所在的数据库的名称，或指定数据表或检视表的拥有者。如果不指定数据库名称，数据表或检视表所在的数据库即为使用者登入时定义的预设数据库， 第三十四章 会详细讨论使用者定义。另一种方法为使用查询指定数据表或检视表，使用这种方法可以指定要在资料表或检视表撷取的资料。（table/view_definition参数可用来指定用于撷取或插入数据的数据表或检视表。）查询必须加上双引号，可以为加上或不加上 ORDER BY 子句的 SELECT 陈述式。指定查询定义时，必须同时指定queryout参数（将在表 24-1 讨论）。在大量复制操作中数据文件的位置可用data_file参数指定，但所指定的路径必须是有效路径。最后，可指定一个或多个列在表 24-1 的参数表24-1 指定大量复制的参数参数 描述In 从档案夹中大量复制数据到 SQL Server 数据库的数据表或检视表中。Out 从 SQL Server 数据表或检视表撷取及大量复制数据到档案夹中。queryout 指定在查询大量复制数据时，才从 SQL Server 数据库中撷取查询所定义的数据，当数据经撷取查询所选出后，会大量复制到档案夹。Format BCP 会建立新的格式文件来执行大量复制作业，所指定的格式化选项（-n、-c、-w、-6 或 -N）和资料表或检视表的分隔符可用于建立格式档。如果使用了format参数，-f 选项也必须同时指定。格式档能让您储存 BCP 定义，之后当您再度使用 BCP 时，就不需要重复定义。选择性使用的参数 您可选择使用列在表 24-2 的参数以修改 BCP 执行大量复制的方式。表24-2 指定大量复制的选择性参数参数 描述-a packet_size 指定在客户端和服务器端之间经由网络封包所传送的字节数目。-b batch_size 指定每一笔批次操作所复制的数据列数，每一笔批次操作皆视为一笔交易。根据默认值，指定数据文件内的所有数据列会被当成同一笔批次操作进行复制，因此若执行大量插入数据，可能会指定此选项，以使每一笔批次操作在执行完成时，解除资料表锁定，这样便能执行其它处理程序（也就是使资源锁定的时间降低）。-c 指定 BCP 使用 char 字符数据型别来执行大量复制作业。-e err_file 指定 BCP 错误文件记录的路径。-f format_file 指定 BCP 所使用过的格式文件的路径。如果 BCP 执行时使用format选项，就会建立之前所提到的格式档（format file）。如果使用了该格式档，就不需使用其它格式选项。-h "hint[,...n]" 指定大量复制时所使用的提示，以下为可选择的提示： • ORDER（column[ASC | DESC]） 指定数据行中的数据排序。 • ROWS_PER_BATCH = number 指定每个批次操作的数据列数目这个选项和 -b 类似，但不应和 -b 联合一起使用。-b 选项将数据列指定的批次操作视为单一交易，传送到到 SQL Server。在未指定 -b 时，整个数据文件夹会视为单一交易，传送到 SQL Server，并使用 ROWS_PER_BATCH 评估测量加载大小尺寸，使大量加载最佳化。 • KILOBYTES_PER_BATCH = number 指定每笔批次操作的千字节（KB）大约数目，此选项和 -b 类似，不同之处在于此选项以千字节 KB 为单位指定批次操作大小。 • TABLOCK 指定在大量复制加载期间使用数据表层级的锁定。这个提示可大为提高效能，因为减少了数据表的锁定资源竞争只在大量复制作业期间保持锁定可降低数据表的锁定争论。 • CHECK_CONSTRAINTS 指定大量复制加载期间检视条件约束。根据默认值，会忽略这些条件约束。 -i input_file 指定响应档的名称。响应文件中包含使用互动模式执行 BCP 时，对每个字段的指令提示 BCP 问题的响应。-k 指定空的数据行应使用保留 Null 值，而不使用是默认值。-m max_errors 指定在结束 BCP 操作取消大量复制作业之前，允许发生错误的最多次数。如果若未包含这个选项，则默认值为 10。-n 指定 BCP 使用 原生（native） 数据型别。-o output_file 指定接收 BCP 输出的输出档。这个输出文件可用一般的文字文件开启，如 Microsoft 的 Notepad 或其它工具记事本等等。-q 指定当数据表或检视表名称包含非 ANSI 字符时（例如空格键），必须使用引号识别项双引号。-r row_term 指定数据列终止符号，默认值是新行字符（Newline Character，/n）。-t field_term 指定字段终止符号（也称为分隔符），默认值是 tTab 字符。-v 打印 BCP 的版本编号和版权信息。-w 指定 BCP 使用 Unicode 字符执行大量复制作业。-C code_page 指定数据文件中数据的字码页（Code Page）。-E 指定在被复制的汇入的档案中包含呈现识别 Identity 数据行的值。-F first_row 指定大量复制操作的起始第一个数据列编号。若未指定数据列编号，第一个数据列的默认值为 1。如果要跳过在数据文件的标题信息，这个选项相当适合。-L last_row 指定大量复制操作最后结束的数据列编号，默认值为 0（0 表示结束的数据列就是数据文件的最后一个数据列），表示指定数据文件的最后一个数据列。如果只想复制特定数目的数据列，这个选项相当适合。-N 指定 BCP 对于非字符数据使用原生数据型别；对于 Unicode 字符数据使用数据的原生数据型别，而对于字符数据则使用 Unicode 来执行大量复制作业。-P password 指定在 -U 选项中使用者账号登入识别码的密码。-R 针对客户端系统计算机地区设定所定义的区域格式，指定使用的货币、日期和时间数据。-S server_name 指定要用于加载的服务器名称。-T 指定可使用的信任联机。使用该选项后，就不需使用login_id和password变量；使用网络使用者安全凭证即可。-U login_id 指定联机至 SQL Server 时所用的使用者账号登入识别码。-V 60|65|70 指定使用旧版 SQL Server 的数据型别来执行大量复制作业。使用这个选项时请与字符 -c 或 -n 选项一起使用。-6 指定 BCP 使用 SQL Server 6.0 或 SQL Server 6.5 数据型别来执行大量复制作业。在使用 BCP 时，您可以使用单一选项或结合数个选项指定 BCP 的执行方式，取得 BCP 强大的优点。以下我们将透过范例学习使用这些选项。使用 BCP 本节我们会学习使用 BCP 将数据加载或输出 SQL Server 的几个范例。这些范例并不包含所有的 BCP 使用范围，但足以示范 BCP 可使用的几个模式和方法。您可以利用之前叙述的指令行参数，或以交互式的方式使用 BCP。要在非互动模式下使用 BCP，必须指定 -n、-c、-w 或 -N 参数。如果没有指定这些参数，BCP 会在互动模式中执行。________________________________________说明 以下所有范例将使用 Northwind 数据库中的Customers数据表。________________________________________使用 BCP 互动模式加载数据 在互动模式下使用 BCP 加载数据有一定的难度，因为使用时要先指定数据行长度与数据型别。当您使用指令行选项时，则不需指定这些数据，相关说明会在下一节讨论。在互动模式下使用 BCP 加载数据并不是很好的方法，以下将示范 BCP 如何在互动模式下运作，在范例中，我们会将 data2.file 数据文件中的数据复制到 Northwind 数据库的Customers数据表中，并将错误写入 err.file 档案中。您必须先建立 data2.file，该档案包含了要加载的数据。要使用系统管理员启动互动模式，请输入下列指令：bcp Northwind.dbo.Customers in data2.file -e err.file -Usa________________________________________译注 如果出现无法连接 SQL Server 的错误讯息，可能是您没有指定 SQL Server 的执行个体，请使用 -S 选项指定安装在您机器上的 SQL Server 执行个体。________________________________________接下来会出现提示要您输入密码，请输入系统管理员（sa）的密码。BCP 会接着要您提供欲复制数据的信息，以下为互动阶段的范例：请输入字段 CustomerID [nchar] 的档案储存类型：char请输入字段 CustomerID [1] 的前置长度：0请输入字段长度 CustomerID [26]：5请输入字段结束符号 [none]：,
请输入字段 CompanyName [nvarchar] 的档案储存类型：char请输入字段 CompanyName [1] 的前置长度：0请输入字段长度 CompanyName [189]：40请输入字段结束符号 [none]：,
请输入字段 ContactName [nvarchar] 的档案储存类型：char请输入字段 ContactName [1] 的前置长度：0请输入字段长度 ContactName [143]：30请输入字段结束符号 [none]：,
请输入字段 ContactTitle [nvarchar] 的档案储存类型：char请输入字段 ContactTitle [1] 的前置长度：0请输入字段长度 ContactTitle [143]：30请输入字段结束符号 [none]：,
请输入字段 Address [nvarchar] 的档案储存类型：char请输入字段 Address [1] 的前置长度：0请输入字段长度 Address [283]：60请输入字段结束符号 [none]：,
请输入字段 City [nvarchar] 的档案储存类型：char请输入字段 City [1] 的前置长度：0请输入字段长度 City [73]：15请输入字段结束符号 [none]：,
请输入字段 Region [nvarchar] 的档案储存类型：char请输入字段 Region [1] 的前置长度：0请输入字段长度 Region [73]：15请输入字段结束符号 [none]：,
请输入字段 PostalCode [nvarchar] 的档案储存类型：char请输入字段 PostalCode [1] 的前置长度：0请输入字段长度 PostalCode [49]：10请输入字段结束符号 [none]：,
请输入字段 Country [nvarchar] 的档案储存类型：char请输入字段 Country [1] 的前置长度：0请输入字段长度 Country [73]：15请输入字段结束符号 [none]：,
请输入字段 Phone [nvarchar] 的档案储存类型：char请输入字段 Phone [1] 的前置长度：0请输入字段长度 Phone [115]：24请输入字段结束符号 [none]：,
请输入字段 Fax [nvarchar] 的档案储存类型：char请输入字段 Fax [1] 的前置长度：0请输入字段长度 Fax [115]：24请输入字段结束符号 [none]：,
您要将这个格式信息存成档案？[Y/n]：Y主檔名 [bcp.fmt]：data.fmt
开始复制…SQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]在 BCP data-file 遇到非预期的 EOF
已复制 5 个数据列。网络封包大小（字节）：4096频率时间（微秒）：总计 471您在开始前必须了解上述这些值。由于没有指定复制数据列的数目，所以当 BCP 执行到档案结尾，便会出现错误讯息。使用 BCP 与指令行选项加载数据 使用BCP加载数据时，使用指令行选项是较为简单的方法，本节使用的范例将使用 BCP 从一个数据文件加载数据，该数据文件使用 Tab 为字段的分隔符，并且字段型别为 char 数据型别。我们将使用 -c 选项指定在数据文件中的数据为 char 格式。使用 -c 选项也使得 BCP 在非互动模式下执行。下列的指令可将在 data.file 数据文件中 25 行的数据，复制到Customers数据表中。bcp Northwind.dbo.Customers in data.file -e err.file -c -Usa由于 -c 选项指定了 char 数据型别，因此不需再设定字段长度或前置长度。假设我们在 data.file 中建立 25 列数据，并且 Tab 字符分隔的数据和Customers数据表中的数据行对应，也输入了 sa 的密码，该工作阶段就会如下所示（视网络封包大小和频率可能有所不同）：开始复制…
已复制 25 个数据列。网络封包大小（字节）：4096频率时间（微秒）：总计 30在这个范例中，复制的方向设定成In，是指数据是被加载数据库。建议您指定一个错误档案，因为错误档案可以记录在 BCP 执行期间产生的任何错误。如果您没有指定错误档案，错误会被送到屏幕上。使用Format选项加载数据 在 〈使用 BCP〉 一节，我们已经建立一个名称为 data.fmt 的格式档案。您可以使用这个档案，无须再手动输入数据型别、前置长度、字段长度或字段终止符号等格式选项。使用 -f 选项就可呼叫这个档案，呼叫方法如下：bcp Northwind.dbo.Customers in data2.file -e err.fil -f data.fmt -L 5 -Usa假设已经输入sa的密码，并且建立了 data2.file，就会看到如以下的显示：开始复制…
已复制 5 个数据列。网络封包大小（字节）：4096频率时间（微秒）：总计 50除了可以使用 -f 选项，范例也包含了 -L 选项，-L 选项标示最后一个从输入文件被复制的数据列。在本例中，-L 选项指定了第 5 个数据列为最后一笔被复制的数据列，因此消除了前面看到过的 EOF 错误讯息。使用 BCP 互动模式撷取数据 在互动模式中使用 BCP 从数据库中撷取数据，会比将数据复制到数据库中简单。原因是当在撷取数据时，BCP 会帮您填入字段长度的选项。现在试着用以下的指令从Customers数据表互动地复制数据。bcp Northwind.dbo.Customers out dataout.dat -e err.file -Usa在输入了这个指令及sa密码后就会执行以下的工作阶段：请输入字段 CustomerID [nchar] 的档案储存类型：char请输入字段 CustomerID [1] 的前置长度：0请输入字段长度 CustomerID [26] ：请输入字段结束符号 [none] ：,
请输入字段 CompanyName [nvarchar] 的档案储存类型：char请输入字段 CompanyName [1] 的前置长度：0请输入字段长度 CompanyName [189]：请输入字段结束符号 [none]：,
请输入字段 ContactName [nvarchar] 的档案储存类型：char请输入字段 ContactName [1] 的前置长度：0请输入字段长度 ContactName [143]：请输入字段结束符号 [none]：,
请输入字段 ContactTitle [nvarchar] 的档案储存类型：char请输入字段 ContactTitle [1] 的前置长度：0请输入字段长度 ContactTitle [143]：请输入字段结束符号 [none]：,
请输入字段 Address [nvarchar] 的档案储存类型：char请输入字段 Address [1] 的前置长度：0请输入字段长度 Address [283]：请输入字段结束符号 [none]：,
请输入字段 City [nvarchar] 的档案储存类型：char请输入字段 City [1] 的前置长度：0请输入字段长度 City [73]：请输入字段结束符号 [none]：,
请输入字段 Region [nvarchar] 的档案储存类型：char请输入字段 Region [1] 的前置长度：0请输入字段长度 Region [73]：请输入字段结束符号 [none]：,
请输入字段 PostalCode [nvarchar] 的档案储存类型：char请输入字段 PostalCode [1] 的前置长度：0请输入字段长度 PostalCode [49]：请输入字段结束符号 [none]：,
请输入字段 Country [nvarchar] 的档案储存类型：char请输入字段 Country [1] 的前置长度：0请输入字段长度 Country [73]：请输入字段结束符号 [none]：,
请输入字段 Phone [nvarchar] 的档案储存类型：char请输入字段 Phone [1] 的前置长度：0请输入字段长度 Phone [115]：请输入字段结束符号 [none]：,
请输入字段 Fax [nvarchar] 的档案储存类型：char请输入字段 Fax [1] 的前置长度：0请输入字段长度 Fax [115]：请输入字段结束符号 [none]：,
您要将这个格式信息存成档案？[Y/n] n主檔名 [bcp.fmt]：
开始复制…
已复制 96 个数据列。网络封包大小（字节）：4096频率时间（微秒）：总计 20这个互动的 BCP 工作阶段建立了一个以逗点分隔字段的档案，该档案为一文字文件，所有的数据皆为字符数据型别，可用记事本检视其内容。不过，BCP 没有在每一行的结尾加上换行字符，因此，当您使用记事本检视数据时，数据行会很长。使用 BCP 与选择性选项撷取数据 要建立一个易于阅读并且加上换行字符的数据文件，您可以使用 -c 选项：bcp Northwind.dbo.Customers out dataout.dat -e err.file -c -Usa在输入指令与sa密码后，工作阶段执行如下：开始复制…
已复制 96 个数据列。网络封包大小（字节）：4096频率时间（微秒）：总计 10使用queryout选项撷取数据 最后我们来看看使用queryout选项撷取数据的范例，这个选项能在复制SQL Server数据库数据时指定查询，指定查询时，可选取特定的数据，并只复制被选取出来的数据。queryout的使用方法很简单，只要在双引号中包含查询即可，方法如下：bcp "SELECT CustomerID, CompanyName FROM Northwind..Customers"queryout dataout.dat -e err.file -c -Usa输入sa密码后，工作阶段显示如下：开始复制…
已复制 96 个数据列。网络封包大小（字节）：4096频率时间（微秒）：总计 1这个查询的输出为 Tab 字符分隔的、并且每列有终止符号的数据文件。该档案的两个字段为：Customen ID和CompanyName。当在数据库撷取特定的数据行或数据列时，这个方法十分有用。BULK INSERT 陈述式 T-SQL 中的 BULK INSERT 陈述式和 BCP 差不多，两者都可大量复制数据文件中的数据到 SQL Server 数据库中。然而 BCP 既可以用于复制数据也可以用于从 SQL Server 取出数据，但 BULK INSERT 只能用于数据的加载。这个限制降低了 BULK INSERT 陈述式的功能性，但是因为该陈述式可在 SQL Server 内部执行为执行绪而不是一个外部程序，所以去除了程序间的数据传送步骤，提高了数据加载的执行效能，也使得用 BULK INSERT 陈述式加载数据比 BCP 更有效率。BULK INSERT 语法 和 BCP 一样，BULK INSERT 陈述式要设定几个必要参数和几个选择性使用的参数。您可以使用以下的指令，在 SQL Server 内部呼叫 BULK INSERT（使用ISQL、OSQL 或 Query Analyzer），其中包含了必要及选择性使用的参数：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]] [[ , ] FIRETRIGGERS [ =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 ] )]必要参数 数据文件的位置可用data_file参数指定，但所指定的路径必须是有效路径。数据插入的数据库位置由数据表定义或检视表来决定。如前所述，您可以指定资料表或检视表所在的数据库名称，或指定数据表或检视表的拥有者。如果您试图用 BULK INSERT 指令将数据插入检视表，只能影响检视表中 FROM 子句所参照底层资料表的其中一个。选择性使用的参数 您可以选择性的使用列在表 24-3 的参数和关键词来修改 BULK INSERT 的执行，这些选项和 BCP 的选项很相似。表24-3 BULK INSERT 选择性使用的参数可选择性使用的参数 描述BATCHSIZE =size 指定每一笔批次操作中的列数，每笔批次操作皆视为一次交易。CHECK_CONSTRAINTS 指定检查条件约束限制，根据默认值，会忽略这些限制。CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] 指定数据文件中的字码页，只有在数据含有char、varchar或text数据型别时，CODEPAGE 才是有用意的。DATAFILETYPE [ = 'char' | 'native' | 'widechar' | 'widenative' ] 指定数据文件中的数据型别，默认值的型别为char，其它的选项还包括native（原生数据型别）、widenative（Unicode 字符）和widenative（和 native 原生数据型别相同，差别在char、varchar和text是以 Unicode 储存）。FIELDTERMINATOR [ =field_term] 指定要用于char与widechar数据型别文件中的字段终止符号端子，默认值是/t（Tab 字符）。FIRSTROW [ =first_row] 复制的第一个数据列编号，默认值为 1，表示指定数据文件中的第一个资料列。如果要跳过在数据文件中的标题信息，就可使用此参数。FORMATFILE [ =format_file] 指定格式文件的路径。KEEPIDENTITY 指定保留在汇入的数据文件中呈现 Identity 识别数据行的值。KEEPNULLS 指定空的数据行应保留 Null 值。KILOBYTES_PER_BATCH [ =number] 指定在大量复制时，每一笔批次操作的千位组（KB）近似数据数。LASTROW [ =last_row] 指定大量复制时，插入的最后一个数据列编号。默认值为 0，表示指定数据文件的最后一个数据列。如果只想复制特定的资料列，可以使用这个选项。MAXERRORS [ =max_errors] 指定在大量复制插入结束之前，允许发生错误的最多次数，默认值为 10。ORDER (column[ASC | DESC] ) 指定数据在数据文件中的排序方式。ROWS_PER_BATCH [ =rows_per_batch]] 指定每一笔批次操作的数据列数目，每一笔批次会被视为单一交易复制。根据预值，所有在数据文件夹的资料列，会被视为单一批次操作插入，执行一次认可。当您执行大量插入时，可考虑指定这个选项，以在执行批次操作时，能解除资料表锁定，允许执行其它处理程序步骤。ROWTERMINATOR [ =row_term] 要用于char与widechar数据型别文件的数据列终止符号端子，默认值是新增行字符（/n）。使用 BULK INSERT 现在来看看使用 BULK INSERT 陈述式的两个范例，在这两个范例中，我们将会从data.file加载数据至 Northwind 数据库的Customers数据表。________________________________________说明 BULK INSERT 陈述式只可用于将数据加载数据库，不可用于撷取数据。由于BULK INSERT 的操作模式不如 BCP 来的多，所以这里的范例会比较少。________________________________________使用下列的 T-SQL 陈述式，将数据加载数据库：BULK INSERT Northwind..Customers FROM 'C:/data.file'WITH ( DATAFILETYPE = 'char' )GO您可依需要加入更多的选项，以下的范例使用更多可选择的参数：BULK INSERT Northwind..Customers FROM 'C:/data.file'WITH ( BATCHSIZE = 5, CHECK_CONSTRAINTS, DATAFILETYPE = 'char', FIELDTERMINATOR = '/t', FIRSTROW = 5, LASTROW = 20, TABLOCK )这个陈述式只会从数据文件加载第 5 到第 20 个资料列。不管是不是默认值，字段终止符号应指定为 Tab 字符。该范例也检测大量插入过程中限制条件，并在加载过程使用数据表锁定，执行加载的交易将每 5 个数据列视为一个批次单位。数据转换服务 数据转换服务（Data Transformation Services，DTS） 是 SQL Server Enterprise Manager 的一部份，让您可以轻松将数据汇入数据库或将数据从数据库汇出。DTS 由两种精灵组成，包括 汇出精灵 和 汇入精灵 。本节将讨论如何使用这两种精灵。汇入精灵 您可以透过使用汇入精灵，将不同来源的数据汇入数据库。汇入精灵和 BCP 与 BULK INSERT 陈述式不同的地方在于，汇入精灵可汇入除了数据文件外其它来源的数据，以下为使用汇入精灵的步骤： 1. 在 Enterprise Manager 中，展开一个服务器群组，并且按一下想将数据汇入的服务器。从 工具 菜单，选择 精灵 ，然后展开 数据转换服务 数据夹，选择 DTS 汇入精灵 ，按一下 确定 。将出现 数据转换服务 ─ 汇入／汇出精灵 画面，如图 24-2 所示。 图24-2 「数据转换服务─汇入／汇出精灵」画面2. 按一下 下一步 以显示 选择数据来源 画面，如图 24-3 所示。 图24-3 「选择数据来源」画面在这里您可以从下拉式清单中选择数据来源。我们选择了图 24-3 中的 Text File 选项，您也可从下列的数据来源选项中选择： o dBase o Microsoft Access o Microsoft Data Link o Microsoft Excel o Microsoft Visual FoxPro o Other（ODBC data source） o Other OLE_DB data source o Paradox o Data files 选项的决定有部分是取决于您在安装系统时所安装的 ODBC 驱动程序，举例来说，如果安装了 Oracle ODBC 的驱动程序，清单中就会列出这个选项， 选择数据来源 画面会依照您所选择的数据来源而改变，无论选择了哪个数据来源，都需要输入档案或登入信息。 3. 按一下 下一步 以显示 选择档案格式 画面，如图 24-4 所示。这个画面只有在您选择 Text File 时才会出现，该画面允许您选择档案格式，画面中的其它选项如下： o 使用分隔符 或 使用固定字段 允许您选择汇入档案的格式，以及特定的分隔字符或字段宽度。 o 档案类型 下拉式清单允许您指定汇入档案格式为 ANSI、OEM 或 Unicode。 o 数据列分隔符 下拉式清单允许您指定汇入档案中用什么符号来结束每一个数据列。 o 文字定位项 下拉式清单用来指定哪一个字符标记要用于分隔的数据文件来限定文字。 o 起始列号 指定开始复制的数据列编号（从档案开头为 0 号算起）。 o 第一列有数据行名称 指定文字文件的第一列为数据行标题而非数据。
图24-4 「选择档案格式」画面4. 选择 使用分隔符 ，以 {CR}{LF} 作为资料列分隔符，将 文字定位项 设成 <无> 。按一下 下一步 以显示 指定数据行分隔符 画面，如图 24-5 所示。该画面是一个指定字段分隔符的简便方法，因为您会依所选择的选项看到选择该选项后实时的画面，以决定是否选择了适当的分隔符。您可以使用 逗号 、 Tab键 、 分号 或 其它 以选择分隔符。当您选择分隔符时，数据列会在预览窗口中显示。如果您选择的是 使用固定字段 而不是 使用分隔符 ，出现的会是 固定字段的数据行位置 画面。 图24-5 「指定数据行分隔符」画面5. 在您选取了分隔符后，按一下 下一步 进入 选择目的地 画面，如图 24-6 所示，在这个画面之下可选择将数据汇入的数据库，您必须指定 SQL Server ODBC 目的地（数据库的 ODBC 别名）、服务器与数据库。本范例中我们会指定 Northwind 数据库，在这个画面中选择是否使用 Windows 或 SQL Server 的身份验证。如果您使用 SQL Server 身份验证，必须在空格中输入 SQL Server 使用者名称和密码，SQL Server 安全性在 第三十四章 会作详细说明。如果提供的使用者名称或密码无效，可以按一下 重新整理 按钮重试。要变更其它的属性，例如安全性选项、联机逾时等等，按一下 进阶 按钮修改，不过这些属性通常不需要更改。 6. 按一下 下一步 以显示 选取来源数据表和检视表 画面，如图 24-7 所示。在这个画面中，您可以从 数据表和检视表 中的 目的地 字段，呼叫出下拉式清单，选择数据要汇入的数据表。在这里按一下 预览 按钮，可以预览数据。 全选 和 全部不选 按钮可使您选择全部的数据表或都不选择。 图24-6 「选择目的地」画面
图24-7 「选取来源资料表和检视表」画面7. 在相同的画面中，您可以存取转换服务，此选项可在执行数据汇入时，允许数据间的转换（更改数据行等）。要执行这个项目，按一下 转换 按钮（ 转换 标题下有三个点的那个按钮），进入 数据行对应与转换 对话框，如图 24-8 所示。在 数据行对应 卷标页中，可以选择 建立目的资料表 、 删除目的表中的数据表或数据列 、 附加数据列到目的数据表 ，预设为 附加数据列到目的数据表 。如果选择 建立目的数据表 ，可以使用 编辑SQL 按钮检视及修改用于建立数据表的 SQL 陈述式。 图24-8 「数据行对应与转换」对话框内的「数据行对应」卷标页8. 选择 转换 卷标页检视转换选项，如图 24-9 所示。在此标签页中，可选择 直接将来源数据行复制到目的数据行 或 在复制到目的地时进行信息转换 。 转换服务 的精确度转换可以在这里指定（如从 16-bit 到 32-bit；从 32-bit 到 16-bit）。NULL 值的转换也可在这里执行（如从 NOT NULL 到 NULL；从 NULL 到 NOT NULL）。 图24-9 「数据行对应与转换」对话框内的「转换」标签页9. 按一下 确定 结束对话框，接着按一下 下一步 进入 储存、排程和复制封装 画面（如图 24-10 所示），这个画面可以决定是否现在就执行汇入，还是要安排汇入的排程。在这里也可选择 储存 DTS 封装 以便日后再次执行此汇入程序。要选择这个项目，请按一下 储存 DTS 封装 复选框（显示在下方的 储存 区域中），选取后就可储存之前选取过的所有转换服务设定。在这里我们选择 立即执行 。 10. 按一下 下一步 进入 正在完成 DTS 汇入／汇出精灵 （如图 24-11 所示），按一下 完成 执行汇入。 图24-10 「储存、排程和复制封装」画面
图24-11 「正在完成 DTS 汇入／汇出精灵」画面11. 按一下 完成 后，会看到 正在执行封装 画面的执行（如图24-12），执行完成后会出现一个讯息，告知您汇入完成或产生错误。 图24-12 「正在执行封装」画面使用 DTS 汇入数据很简单，但是若需要重复执行数据的汇入，建立程序代码来汇入会更有效率。您可以使用 BULK INSERT 陈述式储存在一个.sql档案内的方式，建立一个指令码档案。汇出精灵 使用 汇出精灵 可将数据从数据库中汇出到外部的目的地。 汇出精灵 和 BCP 不同，可以将数据汇出到除了数据文件以外的目的地。以下为使用 汇出精灵 的步骤： 1. 在 Enterprise Manager 中，展开一个服务器群组，并且选择想将数据汇出的服务器。从 工具 菜单中，选择 精灵 ，然后展开 数据转换服务 数据夹，选择 DTS 汇出精灵 ，按一下 确定 ，会出现 数据转换服务 ─ 汇入／汇出精灵 画面，如图 24-13 所示。 2. 按一下 下一步 进入 选择数据来源 画面，如图 24-14 所示。在这里可以指定数据来源，您可以保留默认值 Microsoft OLE DB Provider For SQL Server 这个设定，或是选择 Microsoft ODBC Driver For SQL Server 。这两项选择都可以和 SQL Server 联机。其它的选项可以用于从其它种类的数据库汇出数据。选择一个数据库继续汇出步骤，在本例中我们选择 Northwind 数据库。您可以按 进阶 按钮更改进阶的选项，如联机逾时、网络位置、网络链接库和工作站 ID 等，不过这些属性通常不需要更改。 图24-13 「数据转换服务─汇入／汇出精灵」画面
图24-14 「选择数据来源」画面3. 按一下 下一步 进入 选择目的地 画面，如图 24-15 所示。这个画面会依照选择的目的地而有所不同，但在大部分的情况下，会要求您输入登入及档案信息。在本例中我们选择 Text File 为目的地，省略输入登入及档案信息的要求，并将数据表储存成文字文件。在 文件名称 文字方块中输入目的地文件名称。 图24-15 「选择目的地」画面4. 按一下 下一步 进入 指定数据表复制或查询 画面，如图 24-16 所示。这个画面可指定是否汇出整个数据表，还是利用查询汇出部分数据表的数据。如果您是选择了其它的 SQL Server 数据库为输出目的地，那么 在 SQL Server 数据库之间进行对象和数据的复制 这个选项就可以使用。 图24-16 「指定数据表复制或查询」画面5. 如果选择 使用查询来指定传送的数据 后按一下 下一步 的话，会出现 键入 SQL 陈述式 画面，如图 24-17 所示。这里可键入 SQL 陈述式，只选出您想汇出的资料。查询可用于选择数据行或数据列的子集，或是选择整个数据表。 图24-17 「键入 SQL 陈述式」画面6. 选择 下一步 进入 选择目的档案格式 画面，如图 24-18 所示。（如果您是从 键入 SQL Server 陈述式 进入本画面，在 来源 则不会显示下拉式清单。）在这个画面中可以替目的地档案指定数个格式选项，包括档案是否使用分隔符或固定字段。选定格式后按一下 下一步 。 如果您选择的是 复制来源数据库的数据表和检视表 ，按一下 下一步 同样会进入 选择目的档案格式 画面，以这种方法进入这个画面， 来源 就会出现下拉式清单。选定格式后按一下 下一步 。
图24-18 「选择目的档案格式」画面7. 现在进入 储存、排程和复制封装 画面，如图 24-19 所示。这里可以设定是否储存 DTS 封装以便稍后执行，设定方法和之前在 汇入精灵 所介绍的类似，只是这里的选项会以反向方式执行汇出的动作。 图24-19 「储存、排程和复制封装」画面8. 按一下 下一步 进入 正在完成 DTS 汇入／汇出精灵 画面，如图 24-20 所示。 图24-20 「正在完成 DTS 汇入／汇出精灵」画面9. 当您按一下 完成 按钮时，汇出程序开始执行，出现 正在执行封装 画面，如图 24-21 所示，程序完成后会出现汇出成功或失败的讯息。 汇入精灵 和 汇出精灵 的使用简化了许多设定上繁琐的步骤，但请您记得如果需要重复执行汇入或汇出的动作，花些时间写指令码会节省重复执行的时间。您可以建立使用 BULK INSERT 陈述式执行汇入或使用 SELECT 陈述式指定汇出的指令码。________________________________________说明 在范例中我们皆使用文字文件与数据库间的转换，事实上精灵可以执行更多数据型别的转换，特别对于数据库或其它数据来源之间转换数据，精灵尤其有用。________________________________________
图24-21 「正在执行封装」画面Staging 资料表 Staging 数据表是一个暂存数据表，您可以用来建立、处理和操作将会加载 SQL Server 的数据，并且将这些数据复制到数据库内部的适当数据表。本节会学习如何使用 Staging 数据表。Staging 数据表基础 Staging 数据表是一个在数据库中的临时文件储存区，您可以将数据复制进Staging 数据表。然后您可以利用 T-SQL 执行如关联性的操作、将数据转换成想要的格式等等。这样的转换会需要用到 Staging 数据表和其它已存在的数据表。使用 Staging 数据表处理加载的数据，可以免除某些数据加载方法对加载的限制。大部分的数据加载技术只能让数据复制到数据库，而没有加以处理。使用 DTS，可以执行一些数据转换，但是无法转换在数据库中的现有数据。使用 Staging 资料表的主要优点就是您可以基于在 Staging 数据表本身的信息或现存数据表中的信息，执行联结操作。使用 Staging 数据表 本节中会用三个范例示范使用 Staging 数据表，分别是合并和加载数据表、加载及分割数据表、加载唯一值到一个数据表，这三个范例可以示范 Staging 数据表如何辅助数据的加载。合并和加载数据表 想象一个在数据超市（Data Mart）中的数据表，该数据表由两个由在线交易（OLTP）系统中的数据表联合组成，该数据表有 A、B、C、D、E 五个资料行。数据行 A、B、C 在同一数据表内，而数据行 C、D、E 则在另一资料表内。这两个输入的数据表都可透过暂存方式，并且使用一个联结操作加载数据超市中，图 24-22 为操作图示。
图24-22 利用 Staging 数据表执行联结操作加载和分割数据表 在第一个范例中，如果依照正常的情况，应该会将数据表加载数据超市中的数个数据表内（为了正规化的目的）。要能轻易完成这个工作，请先将数据复制到 Staging 数据表中，再使用两个查询将 Staging 数据表中的数据加载数据超市中的数据表，如图 24-23 所示。
图24-23 使用 Staging 数据表分割数据加载唯一值到一个数据表 要确保加载资料的唯一性，您可以利用大量复制的方法将数据复制进 Staging 数据表中，然后使用 T-SQL 陈述式将主要数据表中没有的值，从 Staging 数据表插入主要数据表中。当加载的数据可能违反商业规则时，这个选项会很有用。使用以下的陈述式，可以将唯一值从 Staging 数据表中复制到主要数据表：INSERT INTO table (columnA, columnB)SELECT columnA, columnBFROM staging_tableWHERE columnA NOT IN (SELECT columnA FROM table)陈述式看起来复杂，其实只不过是确认主要数据表内有没有和数据行 A 相同的数据，如果没有，则将 Staging 数据表中的两个数据行（A 和 B）加载主数据表中，以确保插入数据值的唯一性。SELECT...INTO 陈述式 使用 SELECT...INTO 陈述式并不算一个正式用来加载数据的方法，比较正确的说法应该说是从已经存在的数据表或 Staging 数据表中取得数据，然后建立一个新数据表，再加载数据的方法。________________________________________说明 由于 SELECT...INTO 陈述式工作有一定限制，数据库选项select into/bulkcopy必须设为 TRUE。要设定该选项，请使用下面的 T-SQL 陈述式：exec sp_dboption <database_name>, "select into/bulkcopy", TRUE________________________________________SELECT...INTO 陈述式的语法如下：SELECT <column_list>INTO <new_table_name><select_clause>select_clause变量指的是可以和 SELECT 陈述式联合使用的陈述式或其它选项，例如 FROM 和 WHERE。SELECT...INTO 陈述式简单易用，如下面的例子。exec sp_dboption "example", "select into/bulkcopy", TRUEGO
SELECT order_id, contact_id, item_id, item_description, amount INTO newsalesFROM stageGO
exec sp_dboption "example", "select into/bulkcopy", FALSEGO在这里的数据库的名称为「example」，建立一个数据表名称为newsales，取出数据的数据表是stage。本章总结 在本章中，我们学到了 BCP、BULK INSERT 陈述式和 DTS 的使用方式，也介绍了 Staging 数据表和 SELECT...INTO 陈述式，这些工具和技术提供很多益处，因为加载数据至数据库正是 DBA 的主要任务之一。 第二十五章 将开始学习分布式交易协调员（MicrosoftDistributed Transaction Coordinator，MSDTC）以及 Microsoft Transaction Server（MTS）。
