SQL Server 数据转换

282第7章SQLServer数据转换SQLServer提供了一种很容易在SQLServer数据库或者非SQLServer数据库和另外一个SQLServer数据库间转换数据的功能，该功能被称为数据转换服务或数据传输服务（DataTransformServices，DTS）。DTS提供了许多传输数据的工具，如表7-1所示。不同的工具适用于不同的情况。表7-1DTS提供的传输数据的工具工具描述DTS导入/导出向导该向导用于将数据复制到SQLServer实例和从该实例中复制数据，以及将转换映射到数据DTS设计器该图形工具用于生成带有工作流和事件驱动逻辑的复杂包，也可以使用DTS设计器编辑和自定义用DTS导入/导出向导创建的包DTS和SQLServer这些选项可用于从SQLServer企业管理器中操作包和访问包信息企业管理器DTS包执行实用工具包括以下实用工具：DTS运行实用工具（用于调度和运行包的对话框）和disrun实用工具（用于运行包的命令提示实用工具）DTS查询设计器该图形工具用于在DTS生成查询在DTS中，使用OLEDB提供者（OLEDBProvider）在不同数据库之间传输和转换数据。通过OLEDB提供者可以访问关系和非关系型数据。针对每种数据源，都有相应的OLEDB提供者。这些数据源包括文本文件、ODBC数据源（例如Oracle数据库）、OLEDB数据源（例如其他SQLServer实例）、ASCII文本文件和Excel电子表格。此外，SQLServer复制使数据得以在整个企业内发布，在各个位置之间复制以及自动同步不同数据副本之间的更改。DTS处理数据的过程如图7-1所示。图7-1DTS处理数据的过程第1节导入/导出一、导入/导出基本概念导入导出是SQLServer数据库系统与外部进行数据交换的操作。导入数据是从外部其他系统数据源中检索数据，并将数据插入到SQLServer表的过程，例如将Excel表中数据引入到SQLServer数据库。导出数据是将SQLServer数据库中的数据转换为某些用户指定格式的过程，如将SQLServer表的内容导入到MicrosoftAccess数据库中，或者将一个SQLServer的数据库转移到SQLServer。SQLServer可以导入的数据源包括ODBC数据源（例如Oracle数据库）、OLEDB数据源（例如其他SQLServer实例）、ASCII文本文件和Excel电子表格，也可以将SQLServer的数据导出为这些格式。SQLServer2000中有多个常用的支持数据导入导出的组件，它们是：2831．数据转换服务（DTS）数据转换服务在异类OLEDB和ODBC数据源之间导入和导出数据。SQLServer2000提供了导入\导出向导（DTSImport/ExportWizard）、DTS设计器（DTSDesigner）、DTS包执行实用工具及DTS查询设计器等用于创建、调度和执行DTS包的工具等等。2．复制复制用于创建单独数据库中的数据副本，并通过将一个副本中的修改复制到其他所有副本，使所有副本同步。3．批量复制程序（BulkCopyProgram,BCP大容量复制）SQLServer的大容量复制功能允许高效地传输大量数据。大容量复制一次性地将数据传入一个表，或从一个表中传出。常用bcp实用工具批量复制数据,该实用程序通常用于将文本文件数据引入到SQLServer表，或将数据从SQLServer表导出到一个文本文件中。4．T-SQL语句T-SQL语句包括SELECTINTO、INSERTSELECT、BULKINSERT、BACKUP和RESTORE语句。二、使用bcp实用程序导入导出数据bcp(blockcopy)是SQLServer系统提供的一个块拷贝实用程序，其功能是将数据库表中的数据拷贝到某个数据文件或将某个数据文件的数据拷贝到数据库表中。常使用ASCII文本文件与数据库表进行交换。bcp实用程序是一个非常灵活的工具，是一个命令行实用程序，必须在操作系统提示符下执行。其语法格式如下：bcp{[[database_name.][owner].]{table_name|view_name}|query}{in|out|queryout|format}data_file[-mmax_errors][-fformat_file][-eerr_file][-Ffirst_row][-L_last_low][-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][-hhint[,...n]]主要参数的含义如下：（1）database_name：指定的表或视图所在数据库的名称。如果未指定，则为用户默认数据库。（2）Owner：表或视图所有者的名称。（3）table_name|View_name|query：是使用bcp实用程序所涉及到的表名，当将数据导入到SQLServer系统时，它是目标表名：当从SQLServer系统中导出数据到外部时，它是源表（或视图）名。query是一个查询，表示从该查询返回的结果集中进行大容量复制，要与queryout选项一起使用。（4）in|out|format|queryout：指出拷贝的方向（相当于SQLServer系统）。in表示把文件中的数据拷贝到数据库表中；out表示把数据库表中的数据拷贝到文件中；format表示根据选项（－n、-c、－w、－6或-N）及表分界符创建一个格式文件，若format选项，则必须使用－f选项。选项queryout与query一起使用。284（5）data_file：是从系统拷贝数据或从磁盘文件向系统拷贝数据时使用的文件的完整路径名。当向SQLServer系统拷贝数据时，该文件包含了要拷贝到SQLServer系统的数据；当从SQLServer系统中向外部拷贝数据时，该文件是目标文件。文件名的总长度不超过255个字符。（6）－mmax_errors：指定在拷贝操作取消之前可以发生的最多错误数，默认值是10。（7）－fformat_file：是格式文件的完整路径名，当使用由formar选项所创建的格式文件大容量复制入或复制出数据时，使用此选项。（8）－eerr_file：指定错误文件的完整路径，此错误文件用于存储无法从文件传输到数据库的所有行。（9）-Ffirst_row：指定要大容量复制的第一行的序数，默认值是1，表示在指定数据文件的第一行。（10）－Llast_row：指定要大容量复制的最后一行的序数，默认值是0，表示指定数据文件中的最后一行。（11）-C：使用字符数据类型执行大容量复制操作。（12）-W：使用Unicode字符执行大容量复制操作。（13）-N：对非字符数据使用数据的本机（数据库）数据类型和对字符数据使用Unicode字符类型执行大容量复制操作。（7）－6：表示使用SQLServer6.0或SQLServer6.5数据类型执行大容量复制操作，该选项仅为保持向后兼容性，应改为使用－V选项。（15）－V（60|65|70）：表示使用SQLServer早期版本中的数据类型执行大容量复制操作，此选项与字符（-C）或本机（-n）格式一起使用。（16）-tfield_term：指定字段之间的分隔，默认值是\t.（17）-rrow_term：指定行结束符，默认值是\n.（18）-iinput_file：指定响应文件的名称，使用交互模式（未使用-n、-c、-w、-N或－6选项）执行大容量复制时，响应文件包含对每一字段命令提示问题的响应。（19）-ooutput_file：指定接收bcp实用程序输出的文件名。（20）-Sserver_name：指定要连接到的SQLServer的服务器名，默认服务器是运行SQLServer系统的本地服务器。（21）-Ulogin_id：指定用于连接到SQLServer的登录帐号。（22）-Ppassword：指定登录帐号怕密码，若不给出，则bcp实用程序提示用户输入密码。（23）－T：指定SQLServer使用信任连接、网络安全用户连接，不需要登录帐号和密码。（24）－v：报告bcp实用程序的版本号和版权。（25）－R：指定使用为客户端计算机的区域设置定义的区域格式，将货币、日期和时间数据大容量复制到SQLServer中。默认情况下，将忽略区域设置。（26）－k：指定在大容量复制操作中空列应保留一个空值，而不是对插入的列赋予默认值。（27）－E：指定标识列的值出现在要导入的文件中时的处理方式，如果没有给出该选项，则正导入的数据文件中此列的标识值将被忽略，且SQLServer2000会根据创建表期间指定的种子值和增量值自动指派惟一的值。如果数据文件的表或视图中不包含标识列的值，则使用格式文件指定导入数据时应跳过表或视图中的标识列；SQLServer2000将自动为该列指派惟一值。（28）－hhint[,...n]:指定在大容量复制数据到表或视图时所使用的提示。【例7.1】使用bcp实用程序导入数据的命令，将文本文件e:\sqldata\student.txt中的数据导入数据库XSCJ的XS表中，该文本文件中各字段值用逗号分隔，每行以换行符结束。命令代码如下：285Bcp’学生管理..XS’ind:\aa.txt-c-t’,’注意：必须在一个完整行中输入该命令，不能加入任何硬回车。第2节数据转换服务一、数据转换介绍数据转换服务（DTS，DataTransferService）提供了在SQLServer与OLEDB、开放式数据库互连（ODBC）或文本文件之间导入、导出和转换数据的功能。使用DTS可以在不同的数据库管理系统之间复制表结构和数据，创建可以集成到第三方产品中的自定义转换对象，或通过交互方式或按规划自动从多个异构的数据源引入和传输数据，从而可在SQLServer中建立数据仓库和数据中心。DTS在异构的源和目标之间只能移动表结构和数据，而当数据源和目标都是SQLServer2000时，除了可传输表结构和数据外，还可以传输触发器、存储过程、规则、默认值、约束条件和用户的数据类型。DTS支持有序而有控制的导入数据，使用DTS时，用户创建和执行DTS包，DTS包全面地描述了在数据传输和转换处理过程中所要完成的工作。使用DTS时，数据源和目标可以是不同的数据库系统，不要求必须有SQLServer作为数据源或目标。OLEDB是对ODBC的进一步扩展，ODBC仅限于基于SQL的关系数据库，而OLEDB提供了访问各种数据格式的方法。DTS包括一套用于创建、调度和执行DTS包工具：DTS导入导出向导（DTSImportWizard,DTSExportWizard）、DTS传输管理器（DTSTransferManager）和DTS包执行实用工具，可通过SQLServer企业管理器来使用这些工具。二、DTS导入导出向导DTS导入/导出向导为在OLEDB数据源之间复制数据提供了最简单的方法，它可以在异构数据环境中拷贝数据、表或查询结果集，并可以交互式地指导用记完成整个复制和转换数据的过程。使用DTS导入/导出向导，可以在20多种不同的数据源及数据目标之间进行数据转换，主要包括：大多数的OLEDB和ODBC数据源以及用户指定的OLEDB数据源（包括MicrosoftODBCDriverforOracle、MicrosoftODBCDriverforSQLServer、MicrosoftOLEDBProviderforOLAPSwrvices、MicrosoftOLEDBProvideforOracle、MicrosoftOLEDBProvide