第12学时导入和导出数据

第12学时导入和导出数据
数据库操作者所面临的最大问题之一是大多数公司使用的操作系统不止一种。虽然实际
操作这些系统并不困难，但是在不同的系统中共享数据将是个问题。在老版本的系统中，你
不得不找一个方法来通过手工操作把数据从一个系统中导出，然后再导入到另一个系统中。
这通常意味着要有一个对两个操作系统的需求和操作都擅长的操作者，并通过他的操作来决
定数据如何移出和载入。这个互用性的问题虽然在SQL 7.0中还没有得到彻底的解决，但操作
变得简单多了。
本学时的要点包括：
• 互用性
• 数据转换服务概述
• 数据转换服务
• 批拷贝程序
• BULK INSERT
12.1 互用性
你也许会感到惊奇，为什么在不同的系统中，互用性会很重要。互用性可以减少你的用
户将不得不去做的大量工作。在大多数机构中，对大型机的依赖仍然比较严重。许多公司在
大型机上仍有数目巨大的应用软件运行着。而且更多的是，这些公司也频繁地使用其他客户
机/服务器数据库平台。你可以设想有一些数据存放在一些不能互相交流的系统中，用户不得
不把同样的数据放入两个不同的操作系统平台中，这样使得他们不得不重复一些枯燥无味的
工作。当用户面临这一问题时，他们就开始抱怨这些额外的工作。这是由D B A出面解决用户
难题的时候。
除了那些为了在不同的数据库平台间移动数据而定制的应用程序外， S Q L有帮你转换数
据的两种主要技术。第一项技术是数据转换服务，简称D T S。这一服务允许你写入一些强有
力的应用程序，叫做“包”。这些“包”涉及到一些多步操作和这些操作步骤有条件的执行。
第二项技术是批拷贝程序( B C P )。B C P作为一个应用工具早在老版本的SQL Sever中已经被广
泛应用。虽然是一项较老的技术，但非常强大。B C P被用于从SQL Sever中提取数据到一个文
件中，然后将这些文件再载入到SQL Sever的表中。
12.2 数据转换服务概述
数据转换服务是SQL Server中提供的一项功能强大的技术，它允许用户从不同的源中导
入、导出和转换数据，这些源可以是SQL Server、任何一种O D B C兼容的数据源、OLE DB兼
容的数据源以及文本文件。D T S提供的一些功能如下：
• 通过交互地或按调度计划从多种不同的源输入和转换数据，以创建数据仓库和数据中心。
• 创建能被集成到第三方应用程序的定制转换包。
• 通过使用第三方OLE DB驱动程序访问数据库，使得这些应用程序能用作数据源和数据
目标。
• 访问本地应用驱动程序，如SQL Server、A c c e s s、E x c e l、O r a c l e。
• 通过内建的数据泵来访问固定长度和定界符的平面文件。
• 将高速、无日志的插入数据添加到数据库中。
• 在多个SQL Server 7.0数据库间传送数据，包括数据库所有的数据和结构，这可以用于
两个服务器间的整个数据库移动。
12.3 数据转换服务
当使用数据传送服务进行工作时，常包括以下两步。第一步是从一个位置到另一个位置
的数据真实移动。第二步通常发生在两位置间，那就是数据的转换。当这两步联系在一起时，
使得D T S有极强的实用性。
数据转换是为了用于将可操作的数据分离出来，变为一种适用于某个特殊应用程序的格
式。数据转换通常涉及某种格式化或修改提取的数据得到合并值或导出值。下面是一些转换
的例子：
• 根据已存在的数据库中的值来计算新值。这种转换包括了数值的集合与求和。
• 把包含单个数值的列分开成不同的列。例如：你可以把一个包含日期的列分成几个不同
列分别包含年、月、日。
• 从几个不同的列中合并数据成一列。例如：合并产品标识号和描述用于某一特定的产品。
• 改变一个列中数据格式。例如：把字母变成大写或把一个列中的数字转化为单词。
现在你对D T S有一定的了解了，实际上应该掌握怎样设置它。SQL Server要求用户通过使
用导入导出向导来交互式地创建对象。这些向导允许你创建D T S包，这些包是主要的D T S对象。
以下步骤通过设置D T S包来从N o r t h Wi n d数据库中导出数据，并拷贝到O r d e r C e n t e r数据库。
1) 打开SQL Enterprise Manager，并且连接到你将要运行D T S向导的服务器上。
2) 连接之后，单击“To o l s”菜单并选择“Wi z a r d”选项，它打开“ Select Wi z a r d”对话
框，如图1 2 - 1。
第1 2学时导入和导出数据1 0 5
下载
图12-1 “Select Wi z a r d”
对话框
3) 单击“ Data Transformation Service ”选项边上的加号( + )，然后点击“ DTS Export
Wi z a r d”，然后点击“O K”按钮。这样Data Transformation Services Export Wi z a r d被打开，如
图1 2 - 2所示。
4) 在介绍窗口中，点击“ N e x t”按钮，打开“ Choose a Data Source”对话框，如图1 2 - 3
所示。在这个屏幕上，你可以选择要导出的数据库。在“ S o u r c e”下拉列表框中，你可以选
择导出数据驱动器。为达到这些目的，选择“ Microsoft OLE DB Provider for SQL Server”。
在“S e r v e r”下拉列表框中，当前使用的服务器将被选中。单击“ D a t a b a s e”下拉列表框旁边
的“R e f r e s h”按钮。这将使得SQL Sever将服务器上所有数据库的名字填入这个下拉列表框。
从这个下拉列表框中选择N o r t h w i n d数据库。当你填完登录信息后，点击“ N e x t”按钮。
5) 接下来显示的是“ Choose a Destination”对话框，如图1 2 - 4所示。从这个屏幕上，能
选择有关你将导出数据的数据库信息。从“ D a t a b a s e”下拉列表框，选择O r d e r C e n t e r数据库，
然后填入登录信息，当完成这些之后选择“ N e x t”按钮。
6) 下一个对话窗口是“ Specify Table Copy or Query”，如图1 2 - 5所示，将会允许你选择要
进行的实际操作。你可以选择拷贝表、用于选择数据的特定查询，或在数据库之间传输对象。
在本操作中，选择“ Copy table from the source database”。
7) 接下来的是如图1 2 - 6所示的“ Select Source Ta b l e”对话框，允许你从源数据库中选择
要拷贝的表。本例中，选择P r o d u c t s表。
1 0 6 SQL Server 7 24学时教程
下载
图12-2 Data Tr a n s f o r m a t -
ion Services Export
Wizard
图12-3 “Choose a Data
S o u r c e”对话框
8) 如果你想在移动数据期间做一些数据转换，请单击有省略号的按钮。它会打开“ D a t a
Mappings and Tr a n s f o r m a t i o n s”对话框，允许你做一些变化。
9) 接下来是如图1 2 - 7所示的“S a v e，Schedule and Replicate Package”屏幕。允许你运行
已创建的包、安排它在以后运行或者保存这个包。如果选择保存这个包，你可以选择它们保
存的地方是SQL Server、R e p o s i t o r y，还是一个文件。
第1 2学时导入和导出数据1 0 7
下载
图12-4 “Choose a Desti
n a t i o n”对话框
图12-5 “ Specify Ta b l e
Copy or Query”对
话框
图12-6 “Select Source
Ta b l e”对话框
10) 如果选择保存包，接下来将是如图1 2 - 8所示的“ Save DTS Package ”屏幕。S Q L
S e r v e r将提供一个缺省名，你可以更改这个名字。
11) 最后显示的屏幕总结了SQL Server创建D T S包的全部过程。在你已经核对了这些数据
后，点击“ F i n i s h”按钮，SQL Server进行包的操作，如图1 2 - 9所示。
另一个从SQL Server导入导出数据的方法是使用批拷贝程序。
12.4 批拷贝程序
批拷贝程序( B C P )是一个较早被用于从SQL Server导入导出数据的工具。尽管它是较旧的
1 0 8 SQL Server 7 24学时教程
下载
图12-7 “S a v e，S c h e d u l e
and Replicate Packa
g e”对话框
图12-8 “Save DTS Packa
g e”对话框
图12-9 D T S包向导正在创建
包
技术，但它仍将适用并且不会过时。B C P使用的是命令行方式，它可以在运行B C P的电脑上
把SQL Server表或视图中的数据提取到一个文件中。该文件可以被发送到另一台电脑或另一
个位置上，并可以导入到另一台电脑上。可以使用的另一个选择是通过使用B C P从一台大型
机或别的数据库应用程序中导出数据，并导入数据到SQL Server中。
使用BCP
正如我前面所提到的， B C P是一个命令行工具。这就意味着有大量的开关选项可以传递
给SQL Server以使它完成你所需要的功能。以下是该命令的语法：
可能的选项如下：
选项描述
t a b l e n a m e 这是你打算通过B C P操作导入或导出的表的名称
v i e w n a m e 这是你打算通过B C P操作导入或导出的视图的名称。假如你准备通过B C P操作导
入数据到视图，你就必须遵循与将数据插入视图相同的原则
" q u e r y " 这是一个可以用于从不同表中指定行和列的S Q L查询。如果指定此选项，同时也
必须指定q u e r y o u t选项
i n 此关键字用于告诉SQL Server将文件内容拷贝到指定的表或视图
o u t 此关键字用于告诉SQL Server将指定的表或视图内容拷贝到文件
q u e r y o u t 此关键字与S Q L查询一起使用，用于告诉SQL Server系统支持查询的运行，并将
结果返回到指定的文件
f o r m a t 这个选项用于让SQL Server生成基于表和视图、指定的代码页、或选定定界符的
格式文件。如果你指定此选项，你同时也必须指定- f选项
d a t a f i l e 这是一个你打算导入或导出数据的文件
-m max_errors 此选项用于指定在全部操作被放弃之前允许出现的错误次数。任何向数据库拷贝
失败的行都会被记作是一次错误。假如该值没指定， SQL Server将使用缺省值1 0
-f format_file 该选项用于指定格式文件的位置， SQL Server用该文件来决定导入或导出数据将
怎样格式化。创建格式文件并不是必须的
-e err_file 该选项用于指定存储B C P过程中出错信息的文件的位置。假如你没有指定它，S Q L
S e r v e r将在屏幕上显示出错信息
-F first_row 该选项用于指定被拷贝数据的首行。缺省值为1，指明你拷贝到服务器上的数据文
件的首行
-L last_row 该选项用于指定被拷贝数据的末行。缺省值为0，指明所有行数据将被处理
-b batch_size 该选项用于指定每批拷贝的行数。每批都作为单独的批拷贝到SQL Server。不要
和- h“R O W S P E R B AT C H = b b”选项一起使用本选项。如果你不指定批，整个过
程将被作为一个单独的事务处理。假如任何情况导致失败， SQL Server都将取消所
做的一切
- n 该选项用于指定使用本地数据库格式的批拷贝。如果指定该选项SQL Server将不提
供任何字段信息
- c 该选项用于指定使用字符格式的批拷贝。SQL Server使用字符数据类型作为存储
类型。\ t ( Ta b键)作为字段定界符， \ n (新行)作为行终止符
第1 2学时导入和导出数据1 0 9
下载
(续)
选项描述
- w 该选项用于指定使用统一字符编码标准字符集进行的B C P操作。该选项不提示字
段类型，它使用n c h a r t数据类型作为存储类型。\ t ( Ta b键)作为字段定界符， \ n (新行)
作为行终止符
- N 该选项用于指定使用本地数据库格式的批拷贝。它同允许使用统一字符编码标准
字符类型的- n选项不同。当使用B C P从早先版本的SQL Server 导入或导出数据时，
此选项无法使用
- 6 该选项用于指定用SQL 6.5的数据类型去执行B C P操作，但必须将- n或- c选项与本
选项一起使用
- q 该选项用于告诉S Q L应该使用引号引起来的标识符。假如你的表或视图名包含了非
A N S I字符集的字符，你必须把它们放在引号中
-C code_page 该选项用于指定将要用于数据文件的代码页。要了解此选项更多的信息，请在B o o k s
O n l i n e中查找“Copying Data between Different Code Pages”
-t field_term 该选项用于指定你要用于数据文件的字段终止符。此选项的缺省值是/ t ,它产生一
个制表位
-r row_term 该选项用于指定你要用于数据文件的行终止符。此选项的缺省值是/ n ,它产生一个
新行
-i input_file 该选项用于指定一个文件，当你在交互模式下运行B C P时，该文件包含了必须提供
的应答
-o output_file 该选项用于指定一个文件，接收从B C P得到的输出。如果未指定该选项，这个信息
会发送到屏幕上
-a packet_size 该选项用于指定SQL Server将通过网络发送的字节数。在大型B C P操作中，加大
包可增加处理的性能。有效字节为5 1 2字节到6 5 5 3 6字节。Windows NT和SQL Sever
都将缺省值定为4 0 9 6字节
-S server_name 该选项用于指定当执行B C P操作时将连接的服务器的名称
-U login_id 该选项用于指定登录的服务器用的登录I D
-P password 该选项用于指定与登录I D相关的口令
- T 该选项用于告诉B C P你将使用一个信任连接与服务器连接
- v 该选项用于让SQL Server返回B C P工具的版本和版权信息
- R 该选项用于指定使用客户机本地的信息将货币、日期和时间数据拷贝到SQL Server
上
- k 该选项用于指定在数据拷贝到表期间，任何空列应保持N U L L值，而不是用这些列
的缺省值。如果你不指定该参数值，SQL Server将把缺省值放入该列
- E 为当前文件中任何相同的列指定值。如果- E未指定，SQL Server将忽略这些相同的
值并创建新值
-h "hint [,...n]" 该选项用于指定在向表或视图进行批拷贝操作时用到的提示。要获得该主题更多
的信息，请在Books Online中查找“BCP Utility”
毋庸置疑，尽管使用B C P时有大量的选项可用，但你可能只会用到它们中的很少部分。
下例是在t o p h a t服务器上使用B C P从p u b s数据库的a u t h o r s表中拷贝出所有行。你可用你服务器
中的名字来替换它们的值。
假如你查看这个文件，可以发现上面的命令将产生以下输出：
1 1 0 SQL Server 7 24学时教程
下载
12.5 BULK INSERT
最后一个用于处理数据迁移的命令是BULK INSERT命令。这是SQL Server7.0中新增的
一条命令。它只用于将数据插入数据库中，并在SQL Server中直接执行。命令的语法如下：
正如你发现的那样，这里的大部分选项与B C P中的一样。为了加载我们前面用B C P命令
所创建的文件，你只需在SQL Server Query Analyzer中运行以下的命令：
12.6 课时小结
在这个学时中，我们讲到一些在导入导出数据时用到的选项。其中最重要和最新的操作
是数据转换服务或称D T S。这项技术非常复杂，并且在你使用它时有很多选项。这里，我仅
仅粗略地介绍了你可能用到的内容。另一些用于导入导出数据的技术是B C P和BULK INSERT
命令。
12.7 专家答疑
问题：我正试着在运行于大型机上的D B 2系统与SQL Server之间移动数据，我应该怎么
办？
解答：你有两种可选择的方法。比较深入的选择是创建一个按所需步骤执行的D T S包。
第1 2学时导入和导出数据1 1 1
下载
另一个选择是使用B C P将D B 2系统中的数据导出，再导入到SQL Server中。
问题：我听说如果执行批拷贝导致数据库故障，将无法恢复这个数据库，是真的吗？
解答：事实上，这不是真的。你可以强迫B C P去执行一些无日志的操作。SQL Server不会
把所有对表的插入都记录到日志。这加速了插入表的操作，但SQL Server将仍留有所有空间
分配操作的日志。如果操作失败， SQL Server能回滚空间分配以使数据库保持一致性。
12.8 课外作业
这些思考题和练习题是供你加深理解用的。答案可以在附录“答案”中找到。
12.8.1 思考题
1) 为什么系统间的互用性很重要？
2) 什么是D T S？
3) DTS 应用程序被称为什么？
4) 三个可用D T S执行的主要功能是什么？
5) 批拷贝程序用于什么目的？
6) 你能对什么数据库对象使用B C P命令？
7) BULK INSERT命令用于什么目的？
12.8.2 练习题
使用导入导出向导创建一个D T S包，使它从p u b s数据库的t i t l e s表中提取所有数据，并导
出到一个文本文件中。