数据库导入导出技术.doc

当我们建立一个数据库时，并且想将分散在各处的不同类型的数据库分类汇总在这个新建的数据库中时，尤其是在进行数据检验、净化和转换时，将会面临很大的挑战。幸好SQL Server为我们提供了强大、丰富的数据导入导出功能，并且在导入导出的同时可以对数据进行灵活的处理。 在SQL Server中主要有三种方式导入导出数据：使用Transact-SQL对数据进行处理；调用命令行工具bcp处理数据；使用数据转换服务(DTS)对数据进行处理。这三种方法各有其特点，下面就它们的主要特点进行比较。 一、使用方式的比较 1. 使用Transact-SQL进行数据导入导出 我们很容易看出，Transact-SQL方法就是通过SQL语句方式将相同或不同类型的数据库中的数据互相导入导出或者汇集在一处的方法。如果是在不同的SQL Server数据库之间进行数据导入导出，那将是非常容易做到的。一般可使用SELECT INTO FROM和INSERT INTO。使用 SELECT INTO FROM时INTO后跟的表必须存在，也就是说它的功能是在导数据之前先建立一个空表，然后再将源表中的数据导入到新建的空表中，这就相当于表的复制（并不会复制表的索引等信息）。而INSERT INTO的功能是将源数据插入到已经存在的表中，可以使用它进行数据合并，如果要更新已经存在的记录，可以使用UPDATE。 SELECT * INTO table2 FROM table1 -table1和table2的表结构相同INSERT INTO table2 SELECT * FROM table3 -table2和table3的表结构相同当在异构数据库之间的进行数据导入导出时，情况会变得复杂得多。首先要解决的是如何打开非SQL Server数据库的问题。 在SQL Server中提供了两个函数可以根据各种类型数据库的OLE DB Provider打开并操作这些数据库，这两个函数是OPENDATASOURCE和OPENROWSET。它们的功能基本上相同，不同之处主要有两点。 (1) 调用方式不同。 OPENDATASOURCE的参数有两个，分别是OLE DB Provider和连接字符串。使用OPENDATASOURCE只相当于引用数据库或者是服务（对于SQL Server、Oracle等数据库来说）。要想引用其中的数据表或视图，必须在OPENDATASOURCE(.)后进行引用。 在SQL Server中通过OPENDATASOURCE查询Access数据库abc.mdb中的table1表 SELECT * FROM OPENDATASOURCE(Microsoft.Jet.OLEDB.4.0,Provider=Microsoft.Jet.OLEDB.4.0;Data Source=abc.mdb;Persist Security Info=False).table1OPENROWSET相当于一个记录集，可以将直接当成一个表或视图使用。 在SQL Server中通过OPENROWSETE查询Access数据库abc.mdb中的table1表 SELECT * FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0, abc.mdb;admin;,SELECT * FROM table1)(2) 灵活度不同。 OPENDATASOURCE只能打开相应数据库中的表或视图，如果需要过滤的话，只能在SQL Server中进行处理。而OPENROWSET可以在打开数据库的同时对其进行过滤，如上面的例子，在OPENROWSET中可以使用SELECT * FROM table1对abc.mdb中的数据表进行查询，而OPENDATASOURCE只能引用table1，而无法查询table1。因此，OPENROWSET比较OPENDATASOURCE更加灵活。 2. 使用命令行bcp导入导出数据 很多大型的系统不仅仅提供了友好的图形用户接口，同时也提供了命令行方式对系统进行控制。在SQL Server中除了可以使用SQL语句对数据进行操作外，还可以使用一个命令行工具bcp对数据进行同样的操作。 bcp是基于DB-Library 客户端库的工具。它的功能十分强大，bcp能够以并行方式将数据从多个客户端大容量复制到单个表中，从而大大提高了装载效率。但在执行并行操作时要注意的是只有使用基于 ODBC 或 SQL OLE DB 的 API 的应用程序才可以执行将数据并行装载到单个表中的操作。 bcp可以将SQL Server中的数据导出到任何OLE DB所支持的数据库的，如下面的语句是将authors表导出到excel文件中 bcp pubs.dbo.authors out c:temp1.xls -c -q -SGNETDATA/GNETDATA -Usa -Ppasswordbcp不仅能够通过命令行执行，同时也可以通过SQL执行，这需要一个系统存储过程xp_cmdshell来实现，如上面的命令可改写为如下形式。 EXEC master.xp_cmdshell bcp pubs.dbo.authors out c:temp1.xls -c -q -SGNETDATA/GNETDATA -Usa -Ppassword3. 使用数据转换服务(DTS)导入导出数据 DTS是SQL Server中导入导出数据的核心，它除有具有SQL和命令行工具bcp相应的功能外，还可以灵活地通过VBScript、JScript等脚本语言对数据进行检验、净化和转换。 SQL Server为DTS提供了图形用户接口，用户可以使用图形界面导入导出数据，并对数据进行相应的处理。同时，DTS还以com组件的形式提供编程接口，也就是说任何支持com组件的开发工具都可以利用com组件使用DTS所提供的功能。DTS在SQL Server中可以保存为不同的形式，可以是包的形式，也可以保存成Visual Basic源程序文件，这样只要在VB中编译便可以使用DTS com组件了。 DTS和其它数据导入导出方式最大的不同就是它可以在处理数据的过程中对每一行数据进行深度处理。以下是一段VBScript代码，这段代码在处DTS理每一条记录时执行，DTSDestination表示目标记录，DTSSource表示源记录，在处理“婚姻状况”时，将源记录中的“婚姻状况”中的0或1转换成目标记录中“已婚”或“未婚”。 Function Main()DTSDestination(姓名) = DTSSource(姓名)DTSDestination(年龄) = DTSSource(年龄)If DTSDestination(婚姻状况) = 1 Then DTSDestination(婚姻状况) = 已婚Else DTSDestination(婚姻状况) = 未婚End IfMain = DTSTransformStat_OKEnd Function上述的三种数据导入导出方法各有其利弊，它们之间的相互比较如图1如示。 二、性能的比较 使用Transact-SQL方式。如果是SQL Server数据库之间的导入导出，速度将非常快，但是使用OPENDATASOURCE和OPENROWSET方法利用OLE DB Provider打开并操作数据库时速度会慢一些。 使用bcp命令方式。如果不需要对数据进行验证等操作的话，使用它还是非常快的，这是因为它的内部使用c接口的DB-library，所以在操作数据库时速度有很大的提升。 使用DTS方式导数据应该是最好的方式了。由于它整合了Microsoft Universal Data Access技术与Microsoft ActiveX技术，因此不仅可以灵活地处理数据，而且在数据导入导出的效率是非常高的。 总结 SQL Server提供了丰富的数据导入导出方法，这给我们提供了更多的选择，但是这又会给我们带来一个新问题：如何根据具体情况选择合适的数据导入导出方法呢？我在这里提供一些个人的建议，希望能对读者起到一定的指导作用。 如果是在SQL Server数据库之间进行数据导入导出时，并且不需要对数据进行复杂的检验，最好使用Transact-SQL方法进行处理，因为在SQL Server数据库之间进行数据操作时,SQL是非常快的。当然，如果要进行复杂的操作，如数据检验、转换等操作时，最好还是使用DTS进行处理，因为DTS不光导数据效率高，而且能够对数据进行深度控制。但是DTS的编程接口是基于com的，并且这个接口十分复杂，因此，使用程序调用DTS将变也会变得很复杂，因此, 当数据量不是很大，并且想将数据导入导出功能加入到程序中，而且没有复杂的数据处理功能时，可以使用OPENDATASOURCE或OPENROWSET进行处理。 bcp命令并不太适合通过程序来调用，如果需要使用批量的方式导数据，可以通过批处理文件调用bcp命令，这样做即不需要编写大量的程序，也无需在企业管理器中通过各种操作界面的切换来进行数据导入导出。因此，它比较适合在客户端未安企业管理器或使用SQL Server Express时对数据进行快速导入导出的场合。 文章来源:-IT动力源（2）插入多个元素到文档中 在下面的示例中，将title和author元素插入到了item节点中。元素之间使用逗号分隔，并包含在括号中。 DECLARE myDoc xmlSET myDoc = SELECT myDocSET myDoc.modify( insert ( SQL Server 2005开发宝典, 张洪举 )into (/root/item)1); SELECT myDoc GO（3）插入属性到文档中 在下面的示例中，向XML文档中插入了多个属性。每次插入属性后，SELECT语句都会显示结果，最终执行结果如图2所示。 DECLARE myDoc xml SET myDoc = Ajax实战 张洪举 SELECT myDocSET myDoc.modify( insert attribute ShipTo 广州into (/root/itemID=1)1); SELECT myDoc - 通过一个sql变量指定要插入属性ShipDate的值 DECLARE ShipDate char(11) SET ShipDate=2006-01-23Z SET myDoc.modify( insert attribute ShipDate sql:variable(ShipDate) cast as xs:date ? into (/root/itemID=1)1) ; SELECT myDoc - 插入多个属性，属性之间使用逗号分隔，并包含在括号内 SET myDoc.modify( insert ( attribute PostCode 253020 , attribute Weight 1.5 ) into (/root/itemID=1)1);SELECT myDocGO图2插入属性到XML中（4）插入注释节点 在下面的示例中，将注释节点插入到ID为2的item节点中title元素的后面。 DECLARE myDoc xml SET myDoc = Ajax实战 张洪举 ASP.NET实战 卢桂章 SET myDoc.modify( insert after (/root/itemID=2/title)1);SELECT myDocGO插入注释后XML的内容如下： Ajax实战 张洪举 ASP.NET实战 卢桂章 （5）使用CDATA部分插入数据 当插入的文本中包含有XML无效字符（如“”）时，可以使用CDATA部分插入数据。参考下面的示例： DECLARE myDoc xml SET myDoc = Ajax实战 张洪举 ASP.NET实战 卢桂章 SET myDoc.modify( insert !CDATA 上门未收 into (/root/itemID=2)1 ) ; SELECT myDoc GO被插入部分中的XML无效字符，会被转换成实体，如“”保存为。下面的插入CDATA部分后XML文档的内容： Ajax实战 张洪举 ASP.NET实战 卢桂章 上门未收 （6）插入文本节点 要将文件插入到XML中，需要使用text函数构造文本，参考下面的示例： DECLARE myDoc xml SET myDoc = Ajax实战 张洪举 SET myDoc.modify(insert text订单列表 as first into (/root)1);SELECT myDocGO得到的XML结果如下： 订单列表Ajax实战张洪举（7）将节点插入类型化的xml列中 在下面的示例中，首先创建了一个架构集合，并建立了一个使用该架构集合的表。在使用Transact-SQL INSERT语句向表中插入一个符合架构约束的XML后，再使用XML DML insert向该XML中插入一个item节点。 - 创建XML架构集合 CREATE XML SCHEMA COLLECTION MySchemasASN ;GO- 创建包含xml数据类型列的表CREATE TABLE MyCustomer (CustomerID int IDENTITY PRIMARY KEY, CustomerItem xml(MySchemas);GO- 向表中插入XML，该XML应当符合/customerschemas命名空间架构的定义INSERT INTO MyCustomerVALUES(N 北方书城 北京市海淀区知春路22号 2222222 刘先生 );- 使用XML DML insert插入另一个item节点到XML中UPDATE MyCustomer SET CustomerItem.modify( declare namespace CS=/customerschemas; insert ( 东图大厦 长春市朝阳大街99号 1111111 孙小姐 ) into (/CS:customer)1 ) WHERE CustomerID=1;SELECT CustomerItem FROM Mycustomer;GO执行上面的SELECT查询后，可以看到CustomerItem中的XML内容，如下所示： 北方书城 北京市海淀区知春路22号 2222222 刘先生 东图大厦 长春市朝阳大街99号 1111111 孙小姐 2delete delete用于删除XML实例的节点。其语法格式如下： delete ExpressionExpression是要删除的节点的XQuery表达式。删除该表达式选择的所有节点，以及所选节点中的所有节点或值。表达式不能是根（/）节点。如果表达式返回空序列，则不进行删除，不返回错误。 下面的示例演示了从非类型化的xml变量中删除指令、注释、属性、元素和节点的方法。在每次删除后都会显示XML，结果如图3所示。 DECLARE myDoc xmlSET myDoc = 这里是文本 Ajax实战 张洪举 ASP.NET实战 卢桂章 SELECT myDoc- 删除注释SET myDoc.modify( delete /root/comment()SELECT myDoc- 删除所有指令SET myDoc.modify( delete /processing-instruction()SELECT myDoc- 删除ID为1的item中的文本节点SET myDoc.modify( delete /root/itemID=1/text()SELECT myDoc- 删除一个属性SET myDoc.modify( delete /root/itemID=1/ShipTo)SELECT myDoc- 删除一个元素SET myDoc.modify( delete /root/itemID=2/author)SELECT myDoc - 删除ID为2的item节点SET myDoc.modify( delete /root/itemID=2)SELECT myDocGO下面的语句演示从类型化XML中删除节点的方法，其中的MyCustomer是前面在“将节点插入类型化的xml列中”部分中创建