第十四章 数据的导入与导出
本章主要介绍SQL Server的数据传输方法和如何选择传输方式，并主要介绍以下三种传输方法：
n 利用批拷贝和BUIK INSERT
n 利用分布式查询
n 利用数据传输服务（DTS）
14.1 数据传输方法
在SQL Server 2000中，有以下三种数据传输功能选择：
n 批拷贝库
n 分布式查询
n 数据传输服务
14.1.1 批拷贝库
批拷贝库是一套API和对象库，它提供从SQL Server 系统中导入和导出数据的功能，在所有的数据传输方法中，批拷贝库是效率最高的一种传输方法，支持大量数据的快速传输，在SQL Server 2000中，有以下库：
n DB-library；DB-library是最初的ATI库，用来对SQL Server进行程序式访问。
n 使用DB-library可以提交查询和检查查询结果，可以使用C++和Visual Basic来使用DB-library。
n SQL-DMO：SQL DMO是SQL分布式管理对象的描写，支持批拷贝操作。
n ODBC API：ODBC API用来访问SQL Server数据库。
n OLE-DB：OLE-DB是微软公司最新的数据访问库。功能非常强大，是DB-library ODBC的替代品。
上面介绍的几种库都是应用于客户端的，本书中主要介绍微软公司提供的批传输方式：BCP和BULK INSERT语句。
BCP（批拷贝程序）是建立在DB-library API基础之上的用DOS会话来输入和输出数据的命令行工具。BCP使用ODBC的批拷贝函数，由于BCP是一个基于DOS的工具，使用起来不是很方便。但是仍然是一种非常有效的数据导入导出工具。
BULK INSERT提供一种不使用命令行，而通过Transact-SQL输入数据的方法，是在SQL Server 7.0中就已经新增的功能。
14.1.2 分布式查询
分布式查询是SQL Server 2000中重要的功能，SQL Server查询分析器支持微软公司最新的数据访问标准OLE-DB。而OLE-DB对关系和非数据的原生数据访问提供非常强大的支持并支持ODBC，从而可以通过OLE-DB访问任何ODBC数据源。
SQL Server 2000提供“联接服务器”的概念，可以由联接服务器提供一个标识名，这个标识名指向一个OLE-DB数据源，这样就可以在查询中使用该标识名来引用数据存储器中的数据结构，也可以使用OPENQUERY和OPENROWSET关键词来检索外部数据。
14.1.3 数据传输服务（DTS）
数据传输服务（Data Transformation Services，DTS）提供了从SQL Server系统中调整、准确灵活地导入和导出数据的功能。支持任何OLE-DB数据源，并具有简单易用的图形化界面。
在数据传输服务中，有一个非常重要的概念“包”，“包”可以从多种数据存储器中导出数据，传输数据类型并产生合计或者统计结果。
在实际应用中，选择哪种传输方法取决于对不同工具的适应性，因为大多数工具都可以提供同样的功能，一般情况下，在选择数据传输方法的时候，可以考虑如下方面：
（1）确定是要输入数据还是输出数据。BCP、DTS和分布式查询都具有导入和导出数据的功能，BULK INSERT命令只能输入数据。
（2）如果是输入数据，确定外部数据的存储位置，如果是输出数据，确定输出数据的存储介质。
（3）确定数据传输中最重要关心的性能指标。BCP和BULK INSERT使用API，性能最好，并且可以从多个客户机上并行加载数据，效率很高。DTS传输数据的复杂性程度比较高，也使用批拷贝API，所以也具有较高的效率。分布式查询没有利用批拷贝API的优点，速度比较慢。
（4）确定是否需要对数据进行处理。BCP一次只能输入或输出一个表的数据，BULK INSERT和分布式查询适合于简单的表单输入。
（5）确定使用图形界面工具还是命令行使用程序。DTE提供了非常友好的图形界面，对于初学者来说，比较容易掌握。其它的都是命令行界面或者Transact-SQL语句，难度较大。
14.2 数据传输服务
数据传输服务（DTS）具有以下三个特点：
n 可以从SQL Server中导入和导出数据。
n 可以在任何OLE-DB提供者之间编写功能强大的数据传输包。
n 可以传输数据库对象。
本节中主要介绍SQL Server Enterprse Manger DTS工具。
14.2.1 DTS导入与导出
SQL Server Enterprise提供了导入（import）和导出（export）两个向导。本质上导入和导出是相同的，都可以从一个OLE-DB或者ODBC数据源拷贝到另外一个OLE-DB或者ODBC数据源。
在使用向导创建DTS包的时候，需要完成以下步骤来定义数据传输：
（1）选择数据源提供者。选择要传输的数据源，也可能需要提供导入数据的格式方面的信息。
（2）选择目的数据提供者。选择数据传输后存放的位置。
（3）明确要拷贝的数据。
（4）明确数据传输的目的端和数据传输。在这一步需要指明传输数据的目的表，传输那些列、源数据列所需要的数据传输等等。
（5）运行、保存预定传输。
也可以使用DTS传输，常用的脚本编写语言有Visual Basic、JavaScript、PeriScript等。可以使用脚本来实现以下传输：
n 改变源数据的数据类型或者格式。
n 将多个源数据列合并成一个目的数据列。
n 将一个源数据列分成多个目的数据列。
n 使用查询表将数据列的值传输成不同的值。
n 对源数据列的值进行有效性检验以满足特定的商业规则。
下面是一个传输样例。在这个例子中，我们从SQL Server的样本数据库Northwind中导出一些信息到Excel电子表格中。导出的信息存储在Northwind数据库的Orders表和它的相关表（Customers、Order Details、Employees）中。导出的数据存放在Orders表中，包括Customer Name、Sales Rep Name、Order Date、RequiredDate和总的Dollar Amount，然后再根据这些数据创建一个pivot表。
操作步骤如下：
（1）运行SQL Server Enterprise Manager，选择数据传输包文件夹，单击鼠标右键，在弹出的菜单中选择所有任务，在所有任务子菜单单击Exprot Data菜单项，打开如图14-1所示的对话框。或者单击Tools菜单，选择Wizards菜单项，打开如图14-2所示的选择向导对话框，在该对话框中单击Data Transformation Services左边的“+”，展开下一级列表，双击DTS Exprot Data，也可以打开如图14-1所示的对话框。
图14-1 DTS向导
图14-2 选择向导对话框
（2）单击“下一步”按钮，弹出如图14-3所示的对话框。在该对话框中，选择源数据库为model。
图14-3 选择数据源
（3）单击“下一步”按钮，弹出如图14-4所示的对话框，目的数据提供者是Excel，在Destination列表中选择Microsoft Excel-97-2000，在File Name文本框中键入输入文件位置和名称或者单击文本框右面的按钮，然后选择输入文件。
（4）设置完成后，单击“下一步”按钮，打开如图14-5所示的对话框，在该对话框中选择要拷贝的数据，选择“Use a query to specify the data to transfer”单选按钮。
图14-4 选择目的端
图14-5 指定拷贝或查询的表
（5）单击“下一步”按钮，打开如图14-6所示的对话框，在该对话框中键入查询的Transact-SQL语句，代码如下：
SELECT Employees.Firstname,Employees.LastName,
Customers.CompanyName,Orders.OrderDate,
Orders.ShipRegion,
SUM(OrderDetails。UnitPrice*OrderDetails.Quantity)OrderTotal
FROM Orders JOIN[Order Details]OrderDetails
ON Orders.OrderID=OrderDetails.OrderID
JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
JOIN Customers
ON Orders.CustomerID=Customers.CustomerID
GROUP BY Employees.FirstName,Employees.LastName,
Customers.CompanyName,Orders.OrderDate,
Orders.ShipRegion
图14-6 使用查询拷贝数据
（6）单击“下一步”按钮，打开如图14-7所示的对话框，在该对话框中指定将数据存放到目的数据源的位置。在这里将传输结果存放到表Order Results中。
图14-7 指定将数据存放到目的数据源的位置
如果要设置数据格式，单击
图14-8 编辑列映射
图14-9 编辑传输脚本
下面列出全部传输脚本：
’************************************************************************
’Visual Basic Transformation Script
’Copy each source column to the
’destination column
’*************************************************************************
Function Main（）
DTSDestination("LastName")=DTSSource("Firstname")
DTSDestination("LastName")=DTSSource("lastName")
DTSDestination("CompanyName")=DTSSource("CompanyName")
DTSDestination("OrderDate")=DTSSource("OrderDate")
DTSDestinaiton("ShipRegion")=DTSSource("ShipRegion")
DTSDestination("OrderTotal")=DTSSource("OrderTotal")
End Function
（7）设置完成后，单击OK按钮，返回图14-7所示的对话框，单击“下一步”按钮，打开如图14-10所示的对话框，默认情况下，Run Immediately复选框被选中，如果希望保存传输包，选中Save DTS Packge复选框。
图14-10 运行保存DTS包
（8）单击“下一步”按钮，打开如图14-11所示的对话框，在Name文本框中键入包的名称，在Description文本框中键入包的描述信息，并选择保存包的SQL Server服务器。
图14-11 选择保存位置并键入描述信息
（9）单击“下一步”按钮，打开完成向导对话框，如图14-12所示，在列表框内列出了设置信息，如果需要改变，单击“上一步”按钮，单击“完成”按钮，弹出如图14-13所示的对话框，显示包的运行进程。运行结束后会显示一个提示对话框，告诉用户传输是否创建成功。如果创建成功，Cancel按钮变成Done按钮，单击Done按钮关闭对话框。
图14-12 完成向导对话框
图14-13 运行包
14.2.2 DTS包
前面介绍了使用DTS输出向导创建包，对于初学者来说，这种方法是比较容易掌握的，但是使用DTS向导不允许在不同的计划之间进行传输，下面将介绍DTS包设计器并用DTS包设计器完成包的创建。
进行SQL Server服务器，连接到相应的服务器，用鼠标右键单击数据传输包文件夹，在弹出的快捷菜单中选择New Package菜单项，打开如图14-14所示的包设计器窗口。
图14-14 包设计器
包设计器窗口主要由以下三部分组成：
n 工具栏：工具栏包括存和查看包的一些常用选项；开始和结束包的命令；创建新数据传输命令等。
n 工具框：工具框中包括所有可以放入包中的组件。分为数据区和任务区，在数据区列出了可以放入包中的连接，在任务区列出了放入包中的任务。
n 设计区域：设计区域是执行操作的主要工作区。设计的时候将连接和任务从工具框中拖动到工作区域，然后用数据传输和链接将它们联系起来。
（1）从工具框中拖动连接到设计区域。在连接放入设计区域后，会弹出一个属性对话框，在该对话框中定义连接的名字以及其它属性。
（2）定义连接需要的数据传输。在设置好连接属性后，可以在连接之间创建数据传输，首先需要选中传输的源数据，然后按住Ctrl键选中传输的目的连接，再点击工具栏中的“传输数据”按钮，这样就会在连接之间画上一条传输线。
@ 双击传输线可以编辑数据传输的属性。
（3）从工具框中将所需要的任务拖到设计区域。定义好包的数据传输后，可以将任务拖到设计区域，双击任务可以编辑它的属性。
（4）定义任务和传输的优先级。创建了任务和连接之后，还需要在任务与任务之间、数据传输和任务之间定义优先级。
@ 按如下操作定义优先级：先选中先发生的对象，按住Ctrl键然后选中第二个发生的对象。然后从工具栏的工作流菜单中选择一个优先级选项，设计区域从先完成的任务或者传输按执行顺序在任务和传输之间画一个箭头。
（5）保存并运行包。定义完成后，选择工具栏上的命令保存或者运行包。
下面举一个例子说明如何使用包设计器创建包，定义包的步骤如下：
（1）在SQL Server Enterprice Manager窗口中，用鼠标右键单击Data Transformations Server，在弹出的快捷菜单中选择New Packge，打开包设计器窗口，如图14-14所示。
（2）将源文件连接类型从工具框中拖到设计区域，弹出如图14-15所示的属性对话框，在该对话框中键入文本文件的路径和格式信息。输入完成后，单击“确定”按钮，返回包设计器窗口。
图14-15 指定文本路径和格式
（3）拖动Microsoft OLE DB Provider for SQL Server连接类型到设计区域。在弹出的属性对话框中命名该连接，并将它指向North Wind数据库，如图14-16所示。
图14-16 设置OLE DB属性
（4）选择文本文件和数据库连接，然后单击工具条中的Transformation Data Task（传输数据任务）按钮，会在文本文件和数据库连接之间出现一个箭头。如图14-17所示。
图14-17 定义传输
双击箭头，会弹出一个属性对话框，如图14-18所示。
图14-18 Transformation Data Task属性
在该对话框中有五个选项卡：Source、Destination、Transformations、Lookups和Options。在Source选项卡中可以键入传输数据的描述信息。
选择Destination选项卡，如图14-19所示，单击Create按钮打开如图14-20所示的对话框，在该对话框中键入SQL语句对表进行定义。定义完成后，单击OK按钮，返回Destination选项卡。
图14-19 Destination选项卡
图14-20 定义表
（5）选择Transformations选项卡，如图14-21所示，在Transformations选项卡中可以定义源和目的之间的传输信息。设置完成后单击“确定”按钮，关闭属性对话框。
图14-21 Transformations选项卡
（6）拖动Execute SQL Statement任务类型到设计区域，弹出一个如图14-22所示的Execute SQL Task属性对话框。在SQL Statement文本框根据需要键入SQL语句。单击“确定”按钮关闭对话框。
图14-22 Execute SQL Task属性对话框
（7）选择目的连接，按住Ctrl键，然后再选择SQL任务。在工作流菜单中选择On Success Precedence选项，这样只有到数据传输结束后SQL任务才能执行。
（8）在工具栏上选择保存按钮，弹出如图14-23所示的保存DTS包对话框，在Package Name文本框中键入包的名称，选择将其保存到SQL Server，并输入密码。设置完成后，单击OK按钮保存包。
图14-23 保存DTS包对话框
14.3 小 结
学习本章，读者应掌握下列内容：
n 在SQL Server中，有三种数据传输功能选择：批拷贝库、分布式查询、数据传输服务。
n 批拷贝库是一套API和对象库，它提供从SQL Server 系统中导入和导出数据的功能，在所有的数据传输方法中，批拷贝库是效率最高的一种传输方法，支持大量数据的快速传输。
n 数据传输服务（Data Transformation Services，DTS）提供了从SQL Server系统中调整、准确灵活的导入和导出数据的功能。支持任何OLE-DB数据源，并具有简单易用的图形化界面。
n 在数据传输服务中，有一个非常重要的概念“包”，“包”可以从多种数据存储器中导出数据，传输数据类型并产生合计或者统计结果。
n SQL Server Enterprise提供了导入（import）和导出（export）两个向导。
n 使用向导创建DTS包的具体步骤。
n 使用DTS包设计器完成包的创建。