SQL Server 数据转换.docx

1、SQL Server 数据转换第 7 章 SQL Server 数据转换SQL Server提供了一种很容易在SQL Server数据库或者非SQL Server数据库和另外一个SQL Server数据库间转换数据的功能，该功能被称为数据转换服务或数据传输服务（Data Transform Services，DTS）。DTS提供了许多传输数据的工具，如表7-1所示。不同的工具适用于不同的情况。表7-1 DTS提供的传输数据的工具工具 描述DTS导入/导出向导 该向导用于将数据复制到SQL Server实例和从该实例中复制数据， 以及将转换映射到数据DTS设计器 该图形工具用于生成带有工作流和事

2、件驱动逻辑的复杂包，也可 以使用DTS设计器编辑和自定义用DTS导入/导出向导创建的包DTS和SQL Server 这些选项可用于从SQL Server企业管理器中操作包和访问包信息企业管理器DTS包执行实用工具 包括以下实用工具：DTS运行实用工具（用于调度和运行包的对话 框）和disrun实用工具（用于运行包的命令提示实用工具）DTS查询设计器 该图形工具用于在DTS生成查询在DTS中，使用OLE DB提供者（OLE DB Provider）在不同数据库之间传输和转换数据。通过OLE DB提供者可以访问关系和非关系型数据。针对每种数据源，都有相应的OLE DB提供者。这些数据源包括文本文件

3、、ODBC数据源（例如Oracle数据库）、OLE DB数据源（例如其他SQL Server实例）、ASCII文本文件和Excel电子表格。此外，SQL Server复制使数据得以在整个企业内发布，在各个位置之间复制以及自动同步不同数据副本之间的更改。DTS处理数据的过程如图7-1所示。图7-1 DTS处理数据的过程第1节 导入/导出一、 导入/导出基本概念 导入导出是SQL Server数据库系统与外部进行数据交换的操作。导入数据是从外部其他系统数据源中检索数据，并将数据插入到SQL Server表的过程，例如将Excel表中数据引入到SQL Server数据库。导出数据是将SQL Serv

4、er数据库中的数据转换为某些用户指定格式的过程，如将SQL Server表的内容导入到Microsoft Access数据库中，或者将一个SQL Server的数据库转移到SQL Server。 SQL Server可以导入的数据源包括ODBC数据源（例如Oracle数据库）、OLE DB数据源（例如其他SQL Server实例）、ASCII文本文件和Excel电子表格，也可以将SQL Server的数据导出为这些格式。SQL Server2000 中有多个常用的支持数据导入导出的组件，它们是：1数据转换服务（DTS）数据转换服务在异类OLE DB和ODBC数据源之间导入和导出数据。SQL S

5、erver 2000提供了导入导出向导（DTS Import/Export Wizard）、DTS设计器（DTS Designer）、DTS包执行实用工具及DTS查询设计器等用于创建、调度和执行DTS包的工具等等。2复制复制用于创建单独数据库中的数据副本，并通过将一个副本中的修改复制到其他所有副本，使所有副本同步。3批量复制程序（Bulk Copy Program,BCP大容量复制）SQL Server的大容量复制功能允许高效地传输大量数据。大容量复制一次性地将数据传入一个表，或从一个表中传出。常用bcp实用工具批量复制数据,该实用程序通常用于将文本文件数据引入到SQL Server表，或将数

6、据从SQL Server表导出到一个文本文件中。4T-SQL 语句T-SQL语句包括SELECT INTO、INSERT SELECT、BULK INSERT、BACKUP和RESTORE语句。二、使用bcp实用程序导入导出数据 bcp(block copy)是SQL Server系统提供的一个块拷贝实用程序，其功能是将数据库表中的数据拷贝到某个数据文件或将某个数据文件的数据拷贝到数据库表中。常使用ASCII文本文件与数据库表进行交换。 bcp实用程序是一个非常灵活的工具，是一个命令行实用程序，必须在操作系统提示符下执行。其语法格式如下： bcp database_name.owner.tab

7、le_name|view_name| query in|out|queryout|format data_file -m max_errors-f format_file-e err_file -F first_row-L_last_low-B batch_size -n-c-w-N-V(60|65|70)-6 -q-C code_page-t field_term-r row_term -i input_file-o output_file-a packet_size -S server_nameinstance_name-U login_id-P password -T-v-R-k-E-h

8、 hint,.n主要参数的含义如下：（1）database_name：指定的表或视图所在数据库的名称。如果未指定，则为用户默认数据库。 （2）Owner：表或视图所有者的名称。（3）table_name|View_name|query：是使用bcp实用程序所涉及到的表名，当将数据导入到SQL Server系统时，它是目标表名：当从SQL Server系统中导出数据到外部时，它是源表（或视图）名。query是一个查询，表示从该查询返回的结果集中进行大容量复制，要与queryout选项一起使用。（4）in|out|format|queryout：指出拷贝的方向（相当于SQL Server系统）。i

9、n表示把文件中的数据拷贝到数据库表中；out表示把数据库表中的数据拷贝到文件中；format表示根据选项（n、-c、w、6或-N）及表分界符创建一个格式文件，若format选项，则必须使用f选项。选项queryout与query一起使用。 （5）data_file：是从系统拷贝数据或从磁盘文件向系统拷贝数据时使用的文件的完整路径名。当向SQL Server系统拷贝数据时，该文件包含了要拷贝到SQL Server系统的数据；当从SQL Server系统中向外部拷贝数据时，该文件是目标文件。文件名的总长度不超过255个字符。（6）m max_errors：指定在拷贝操作取消之前可以发生的最多错误数

10、，默认值是10。（7）f format_file：是格式文件的完整路径名，当使用由formar选项所创建的格式文件大容量复制入或复制出数据时，使用此选项。（8）e err_file：指定错误文件的完整路径，此错误文件用于存储无法从文件传输到数据库的所有行。（9）-F first_row：指定要大容量复制的第一行的序数，默认值是1，表示在指定数据文件的第一行。（10）L last_row：指定要大容量复制的最后一行的序数，默认值是0，表示指定数据文件中的最后一行。（11）-C：使用字符数据类型执行大容量复制操作。（12）-W：使用Unicode字符执行大容量复制操作。（13）-N：对非字符数据使

11、用数据的本机（数据库）数据类型和对字符数据使用Unicode字符类型执行大容量复制操作。（7）6：表示使用SQL Server 6.0或SQL Server 6.5数据类型执行大容量复制操作，该选项仅为保持向后兼容性，应改为使用V选项。（15）V（60|65|70）：表示使用SQL Server早期版本中的数据类型执行大容量复制操作，此选项与字符（-C）或本机（-n）格式一起使用。（16）-t field_term：指定字段之间的分隔，默认值是t.（17）-r row_term：指定行结束符，默认值是n.（18）-i input_file：指定响应文件的名称，使用交互模式（未使用-n、-c、-

12、w、-N或6选项）执行大容量复制时，响应文件包含对每一字段命令提示问题的响应。（19）-o output_file：指定接收bcp实用程序输出的文件名。（20）-S server_name：指定要连接到的SQL Server的服务器名，默认服务器是运行SQL Server系统的本地服务器。（21）-U login_id：指定用于连接到SQL Server的登录帐号。（22）-P password：指定登录帐号怕密码，若不给出，则bcp实用程序提示用户输入密码。（23）T：指定SQL Server使用信任连接、网络安全用户连接，不需要登录帐号和密码。（24）v：报告bcp实用程序的版本号和版权。

13、（25）R：指定使用为客户端计算机的区域设置定义的区域格式，将货币、日期和时间数据大容量复制到SQL Server中。默认情况下，将忽略区域设置。（26）k：指定在大容量复制操作中空列应保留一个空值，而不是对插入的列赋予默认值。（27）E：指定标识列的值出现在要导入的文件中时的处理方式，如果没有给出该选项，则正导入的数据文件中此列的标识值将被忽略，且SQL Server 2000会根据创建表期间指定的种子值和增量值自动指派惟一的值。如果数据文件的表或视图中不包含标识列的值，则使用格式文件指定导入数据时应跳过表或视图中的标识列；SQL Server 2000将自动为该列指派惟一值。（28）hhi

14、nt,.n:指定在大容量复制数据到表或视图时所使用的提示。【例7.1】使用bcp实用程序导入数据的命令，将文本文件e:sqldatastudent.txt中的数据导入数据库XSCJ的XS表中，该文本文件中各字段值用逗号分隔，每行以换行符结束。命令代码如下： Bcp 学生管理.XS in d:aa.txt-c-t,注意：必须在一个完整行中输入该命令，不能加入任何硬回车。第2节 数据转换服务一、数据转换介绍 数据转换服务（DTS，Data Transfer Service）提供了在SQL Server与OLE DB、开放式数据库互连（ODBC）或文本文件之间导入、导出和转换数据的功能。使用DTS可

15、以在不同的数据库管理系统之间复制表结构和数据，创建可以集成到第三方产品中的自定义转换对象，或通过交互方式或按规划自动从多个异构的数据源引入和传输数据，从而可在SQL Server中建立数据仓库和数据中心。DTS在异构的源和目标之间只能移动表结构和数据，而当数据源和目标都是SQL Server 2000时，除了可传输表结构和数据外，还可以传输触发器、存储过程、规则、默认值、约束条件和用户的数据类型。 DTS支持有序而有控制的导入数据，使用DTS时，用户创建和执行DTS包，DTS包全面地描述了在数据传输和转换处理过程中所要完成的工作。使用DTS时，数据源和目标可以是不同的数据库系统，不要求必须有S

16、QL Server作为数据源或目标。OLE DB 是对ODBC的进一步扩展，ODBC仅限于基于SQL 的关系数据库，而OLE DB提供了访问各种数据格式的方法。 DTS 包括一套用于创建、调度和执行DTS包工具：DTS导入导出向导（DTS Import Wizard,DTS Export Wizard）、DTS传输管理器（DTS Transfer Manager）和DTS包执行实用工具，可通过SQL Server企业管理器来使用这些工具。二、DTS导入导出向导 DTS导入/导出向导为在OLE DB数据源之间复制数据提供了最简单的方法，它可以在异构数据环境中拷贝数据、表或查询结果集，并可以交互式

17、地指导用记完成整个复制和转换数据的过程。 使用DTS导入/导出向导，可以在20多种不同的数据源及数据目标之间进行数据转换，主要包括： 大多数的OLE DB和ODBC数据源以及用户指定的OLE DB数据源（包括Microsoft ODBC Driver for Oracle、Microsoft ODBC Driver for SQL Server、Microsoft OLE DB Provider for OLAP Swrvices、Microsoft OLE DB Provide for Oracle、Microsoft OLE DB Provider for SQL Server等） 文本文

18、件 Oracle和Informix数据库 Microsoft Excel 电子表格 Microsoft Access数据库 Microsoft FoxPro数据库 DBase(包括dbaseIII、dbaseIV) Paradox数据库（包括Paradox 3.x、Paradox 4.x、Paradox 5.x） 其他的ODBC数据源 下面详细介绍DTS导入/导出向导的使用方法。1. 启动DTS导入/导出向导 有四种方法启动DTS导入/导出向导，DTS导入/导出向导启动后的第一个窗口如图7-1所示。（1）在开始菜单中选择：程序Microsoft SQL Server导入和导出数据。（2）在SQ

19、L Server企业管理器窗口中选择菜单：工具数据转换服务导入数据（或导出数据）。（3）在SQL Server企业管理器窗口中选择菜单：工具向导数据转换服务导入导出（导入向导）。（4）选择要导入或导出的数据库，单击鼠标右键在“所有任务”快捷菜单中选择导入数据（或导出数据）。图7-1DTS导入/导出向导2使用DTS导入/导出向导进行数据导出操作 下面以将“学籍管理”数据库中的“课程”表中的数据导出到EXCEL表（D:dts课程.xls）中为例，介绍使用DTS导入/导出向导进行数据导出的操作步骤：（1）启动DTS导入/导出向导 进入如图7-1所示的窗口（2）选择数据源 单击“下一步”，出现选择数据

20、源对话框，如图7-2所示。本例数据源应为：用于SQL Server的Microsoft OLE DB提供程序；可以设置不同的认证模式，选择数据库：学籍管理。图7-2（3）选择目的 单击“下一步”，出现选择目的对话框，如图7- 3所示，“目的”选择：Microsoft Excel 97-2000，“文件名”选择： D:dts课程.xls。图7-3（4）指定表复制或查询 单击“下一步”，出现指定表复制或查询的对话框，如图7-4 所示，本例采用从源数据库复制表和视图。图7-4（5）选择源表和视图 单击“下一步”，出现选择源表和视图的对话框，如图7-5所示，本例选中“学籍管理.dbo.课程”表。图7-

21、5（6）保存、调度和复制包 ，单击“下一步”，出现保存、调度和复制包的对话框，如图7-6所示。图7-6（7）数据转换，单击“下一步”，出现如图7-7完成对话框，点击“完成”，系统开始进行数据转换，在随后出现的对话框中分别点击“确定”，出现正在执行包对话框，点击“完成”即可完成数据的导出，出现如图7-8执行包成功的界面。图7-7图7-8经过上述操作后，可以打开D:dts学生.xls表查看其内容。3使用DTS导入/导出向导进行数据导入操作导入数据的操作与导出的操作操作步骤类似，只需要注意源数据和目的数据的类型即可，这里不再赘述，请读者自己尝试进行一些转换。三、数据转换包设计 利用DTS传输和转换数

22、据，包括规划设计转换和创建并执行DTS包。DTS包定义一个或多个数据转换步骤，每一步执行特定的操作，利用它可以将来自不同源的数据转换或合并到一个或多个目标，以满足特定的应用需求。本小节讨论使用DTS包设计器创建、编辑和保存复杂的数据转换。 DTS包设计器是图形用户界面的包编辑器，用于在一个或多个数据库与SQL Server实例之间进行导入、导出以及转换异类数据。使用DTS包设计器可以编辑、保存、执行和调度DTS包。 下面通过一个示例来说明DTS包设计器的使用。本例使用DTS包设计器创建一个DTS包，包的名称为“从EXCEL到SQL Server数据转换”，该包将把一个EXCEL表(d:dts学

23、生信息.xls中的“学生信息”工作表)中的各列转换到SQL Server数据库“学籍管理”的新表中。使用DTS包设计器创建一个DTS包的步骤如下：1打开DTS包设计器 在SQL Server企业管理器控制台目录树的“数据转换服务”文件夹上单击鼠标右键，然后单击“新建包”菜单项，将出现如图7-9所示的包编辑窗口。该窗口包括标题栏、主菜单、快捷工具栏及编辑窗口等四个部分。值得注意的是“连接”、“任务”两个快捷工具栏，它们分别列出了DTS包中可用的连接类型和任务类型。“连接”快捷工具栏中的各连接分别是（鼠标指向相应图标会出现连接或任务名称）：Microsoft OLE DB Provider for

24、 SQL Server、Microsoft Access、Microsoft Excel97/2000、DBase5、HTML File(Source)、paradox 5.x、Text File(Source)、Text File(Destination)、Microsoft ODBC Driver for Oracle、Microsoft Data Link、其他连接。“任务”快捷工具栏中的各任务分别是：文件传输协议任务、ActiveX脚本任务、转换数据任务、执行进程任务、执行SQL任务、数据驱动的查询任务、复制SQL Server对象任务、发送邮件任务、大容量插入任务、执行包任务、消息队

25、列任务、传输数据错误信息任务、传输数据库任务、传输主存储过程任务、传输作业过程。图7-92添加转换数据源连接 在“连接”快捷工具栏中单击Microsoft Excel97/2000图标，出现如图7-10所示的“连接属性”对话框中，点击 “文件名”后的浏览按钮选择“d:dts学生信息.xls”单击“确定”，添加该EXCEL连接。图7-103添加转换目的连接 在“连接”快捷工具栏中单击Microsoft LOE DB Provider for SQL Server图标，在所出现的如图7-11所示的“连接属性”对话框中采用默认的“新建连接”，其默认的数据源类型为“Microsoft OLE DB P

26、rovider for SQL Server”，在“数据库”下拉列表框中指定SQL Server数据库名，本例中数据库名为“学籍管理”，单击“确定”，添加该Microsoft LOE DB Provider for SQL Server连接。图7-114添加删除表任务 在“任务”快捷工具栏中单击“执行SQL任务”图标，将出现如图7-12所示的“执行SQL任务属性”对话框，在“描述”后的文本框中输入“删除学生信息表，在“现有连接”下拉列表框中选择“OLE DB Provider for SQL Server”，在SQL语句文本域中输入以下的SQL语句： drop table 学籍管理.学生信息可

27、单击“分析查询”按钮分析所键入的SQL语句是否有误，出现如图7-13所示对话框，单击“确定”保存该任务。图7-12图7-135按与第四步相同的方法建立名为“创建新表”的执行SQL任务，在SQL语句文本域中输入以下的SQL语句（如图7-14）： create table学籍管理.学生信息 (学号 char(2) NOT NULL, 姓名 char(10) NOT NULL, SQL成绩 float) 这一步操作完成后，包编辑器窗口如图7-15所示。图7-14图7-156保存DTS包 单击快捷工具栏的保存图标，出现如图7-16所示的保存DTS包对话框，在“包名称”文本框中输入所创建包的名称：EXC

28、EL到SQL数据转换，单击“确定”保存，此时，在企业管理器的数据转换服务文件夹的“本地包”中将增加该包。图7-167测试DTS包 在包编辑器窗口的快捷工具栏上单击运行图标，这将对所建立的DTS包进行测试，以确定连接和任务是否正常工作，同时在服务器上创建数据转换时所需的表（本例为学籍管理.学生信息表）。该步操作若正常完成，将出现如图7-17所示的对话框，单击该对话框的“完成”按钮。图7-178建立数据转换任务 先单击快捷工具栏的数据转换任务图标，鼠标的智能菜单会提示建立源连接，本例为：Microsoft EXCEL 97-2000，点击Microsoft EXCEL 97-2000图标即可，在随

29、后的目的连接确定中单击Microsoft OLE DB Provider for SQL Server图标，即可建立起从Microsoft EXCEL 97-2000到Microsoft OLE DB Provider for SQL Server连接的数据转换任务，在两个连接之间出现一个箭头，如图7-18所示。图7-189编辑数据转换任务的属性。在图7-18所示的数据转换任务箭头上单击鼠标右键，在所弹出的快捷菜单上选择属性，将出现如图7-19所示的“数据转换任务属性”对话框。在“源”选项卡中编辑数据源属性：在描述文本框中输入“从EXCEL到SQL数据转换”，并选择EXCEL的“学生信息$”工

30、作表。图7-1910编辑转换结果的属性。在“目的”选项卡编辑目的数据的属性，在表名下拉列表中选择选择学生信息表,如图7-20所示。如果列表中没有,可以点击“新建”按钮，会出现如图7-21所示。图7-20图7-2111编辑转换的属性。在名称中选择一种转换类型，在“转换”选项卡中单击“全选”按钮，将选择源表的所有列，且都导出到目的表中，如图7-22所示，单击“确定” ，至此，DTS 包已经创建完成。图7-2212运行DTS包 单击快捷工具栏的运行图标，将执行正在编辑的DTS包。包执行时，将出现一个对话框，其中有一个进展条，显示包执行的进度，在该对话框中还显示了DTS包中所包含的步骤。执行完毕后，出

31、现一个确认对话框，单击其中的确定按钮，出现如图7-23所示界面。图7-23 接下来可以验证DTS包执行的结果。在SQL Server企业管理器中展开“学籍管理”数据库，可以打开“学生信息“表查看其内容，会发现其内容与d:dts学生信息.xls中数据相同。包创建完成后，在SQL Server企业管理器中可编辑、运行、调度该包，其方法是：在企业管理器中展开数据转换服务文件夹，单击“本地包”，在需进行操作的DTS包上单击鼠标右键，将出现如图7-24所示的快捷菜单，选择相应的菜单项即可。从上面的实例中，直观地了解到一个DTS包描述了民需完成的工作步骤，如本例所创建的包，包含了3个步骤： 图7-24 删除学生信息表； 创建新表; 从EXCEL的表拷贝数据到SQL Server的“学籍管理“数据库的”学生信息“表。 保存DTS 包的好处是可以重用该DTS包。本章小结 SQL Server提供与其他的软件环境之间经常要进行数据的迁移和转换，这就是数据的导入导出，本章详