`
oxidy
  • 浏览: 162922 次
  • 性别: Icon_minigender_1
  • 来自: 吉林长春
社区版块
存档分类
最新评论

SQL Server数据导入导出技术概述与比较

阅读更多
【IT168 技术文档】当我们建立一个数据库时,并且想将分散在各处的不同类型的数据库分类汇总在这个新建的数据库中时,尤其是在进行数据检验、净化和转换时,将会面临很大的挑战。幸好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(...)后进行引用。

  1. 在SQL Server中通过OPENDATASOURCE查询Access数据库abc.mdb中的table1表   
  2.    SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
  3.                'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=abc.mdb;Persist Security  
  4.        Info=False')...table1   
  5.    OPENROWSET相当于一个记录集,可以将直接当成一个表或视图使用。   
  6.    在SQL Server中通过OPENROWSETE查询Access数据库abc.mdb中的table1表   
  7.    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 -S"GNETDATA/GNETDATA" -U"sa" -P"password"

  bcp不仅能够通过命令行执行,同时也可以通过SQL执行,这需要一个系统存储过程xp_cmdshell来实现,如上面的命令可改写为如下形式。

  1. EXEC master..xp_cmdshell 'bcp pubs.dbo.authors out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P"password"'  

 3. 使用数据转换服务(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转换成目标记录中“已婚”或“未婚”。

  1. Function Main()   
  2.   DTSDestination("姓名") = DTSSource("姓名")   
  3.   DTSDestination("年龄") = DTSSource("年龄")   
  4.   If DTSDestination("婚姻状况") = 1 Then  
  5.     DTSDestination("婚姻状况") = "已婚"  
  6.   Else  
  7.     DTSDestination("婚姻状况") = "未婚"  
  8.   End If   
  9.   Main = DTSTransformStat_OK   
  10. End Function  

上述的三种数据导入导出方法各有其利弊,它们之间的相互比较如图1如示。

 

Transact-SQL

命令行工具bcp

DTS

图形管理界面

 无

 无

 有

操作难易程度

 容易

 较复杂

 复杂

编程接口

 SQL

 系统存储过程或通过shell直接调用

 com组件

数据处理

 只能对数据进行简单的过滤

只能对数据进行简单的过滤 

可以利用脚本对数据进行复杂的处理 


  二、性能的比较

  使用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时对数据进行快速导入导出的场合。

分享到:
评论

相关推荐

    SQL SERVER 2000开发与管理应用实例

    本书全面系统地介绍了SQL Server开发和管理的应用技术,涉及安装和配置SQL Server、日期处理、字符处理、排序规则、编号处理、数据统计与汇总、分页处理、树形数据处理、数据导入与导出、作业、数据备份与还原、用户...

    SQL Server 2000简明培训教程

    第1章 SQL Server 关系数据库简介 1.1 SQL Server 特点 1.2 SQL Server的安装和配置 1.3 SQL Server 工具和实用程序 第2章 Transact-SQL语言 2.1 数据定义语言(DDL) ...9.1 数据的导入和导出 9.2 使用DTS设计器

    SQL Server 2008管理员必备指南(超高清PDF)Part1

    11.2 使用SQL Server导入和导出向导创建包 11.2.1 阶段1:数据源和目标的配置 11.2.2 阶段2:复制或查询 11.2.3 阶段3:格式化和转换 11.2.4 阶段4:保存和执行 11.3 了解BCP 11.3.1 BCP 基础 11.3.2 BCP 语法 ...

    SQL Server 2008管理员必备指南(超高清PDF)Part3

    11.2 使用SQL Server导入和导出向导创建包 11.2.1 阶段1:数据源和目标的配置 11.2.2 阶段2:复制或查询 11.2.3 阶段3:格式化和转换 11.2.4 阶段4:保存和执行 11.3 了解BCP 11.3.1 BCP 基础 11.3.2 BCP 语法 ...

    SQL Server 2008管理员必备指南(超高清PDF)Part2

    11.2 使用SQL Server导入和导出向导创建包 11.2.1 阶段1:数据源和目标的配置 11.2.2 阶段2:复制或查询 11.2.3 阶段3:格式化和转换 11.2.4 阶段4:保存和执行 11.3 了解BCP 11.3.1 BCP 基础 11.3.2 BCP 语法 ...

    新员工SQL培训教程 SQL SERVER 2008 简单入门

    SQL SERVER 2008 简单入门 SQL SERVER 培训教程 本教程是针对sql的初级者,提炼出的sql server 2008入门的基本概念和基础操作 目录 数据库概述 3 定义(其一): 3 历史: 3 属性: 3 基本结构 3 数据库管理 5 ...

    SQL.Server.2008管理员必备指南.part2.rar(2/4)

     导入、导出、转换和复制数据  使用SQL Server Management Studio执行主要的管理任务  操作架构、表、索引和视图  进行自动化维护和实现基于策略的管理  监视服务器活动并优化性能  管理日志传送和数据库镜像 ...

    SQL.Server.2008管理员必备指南.part1.rar(1/4)

     导入、导出、转换和复制数据  使用SQL Server Management Studio执行主要的管理任务  操作架构、表、索引和视图  进行自动化维护和实现基于策略的管理  监视服务器活动并优化性能  管理日志传送和数据库镜像 ...

    sqlserver2000基础(高手也有用)

    9.4.3 在客户端实现SQL Server数据导入与导出处理的一些说明 289 9.5 在数据库中存取文件 290 9.5.1 SQL Server中的文件存取方法 290 9.5.2 程序中的文件存取处理方法 292 第 10 章 作业 295 10.1 作业...

    SQL.Server.2008管理员必备指南.part4.rar(4/4)

     导入、导出、转换和复制数据  使用SQL Server Management Studio执行主要的管理任务  操作架构、表、索引和视图  进行自动化维护和实现基于策略的管理  监视服务器活动并优化性能  管理日志传送和数据库镜像 ...

    SQL.Server.2008管理员必备指南.part3.rar(3/4)

     导入、导出、转换和复制数据  使用SQL Server Management Studio执行主要的管理任务  操作架构、表、索引和视图  进行自动化维护和实现基于策略的管理  监视服务器活动并优化性能  管理日志传送和数据库镜像 ...

    精通SQL--结构化查询语言详解

    11.4.3 sql server数据库数据导出 227 11.4.4 sql server数据库数据导入 230 第12章 数据的更新和删除 233 12.1 更新表中的数据 233 12.1.1 update语句的基本语法 233 12.1.2 update语句更新列值 234 12.1.3 ...

    精通SQL 结构化查询语言详解

    11.4.3 SQL Server数据库数据导出  11.4.4 SQL Server数据库数据导入  第12章 数据的更新和删除  12.1 更新表中的数据  12.1.1 UPDATE语句的基本语法 12.1.2 UPDATE语句更新列值  12.1.3 利用子查询...

    精通sql结构化查询语句

    3.3.1 修改数据库语法 3.3.2 使用SQL语句修改数据库 3.4 管理数据库 3.4.1 扩充与压缩数据库 3.4.2 导入与导出数据 3.4.3 数据库的备份与恢复 3.4.4 使用sp_helpdb查看数据库信息 3.5 小结第4章 数据表的相关操作 ...

    sql 数据库

    适合初学者的数据库ppt1. 阐述课程目标:[5分钟] 2 2. 课程知识点讲解: 3 ...2.17.知识点17:使用企业管理器实现数据的导入和导出。[5分钟] 4 3. 本章总结 [10分钟] 5 4. 学员问题汇总: 5 5. 作业: 5

    ASP与SQL网页数据库程序设计

    4-4 服务器网络公用程序 88 4-4-5 客户端网络公用程序 89 4-4-6 服务管理器 89 4-4-7 导入及导出数据 90 4-5 激活服务 91 4-5-1 服务的启动方式 91 4-5-2 SQL Server服务管理器的操作 92...

    Visual C++ 数据库系统开发完全手册.part2

    10.6.2 导入SQL Server数据表 10.6.3 导入其他数据源的数据表 10.6.4 导出数据库 10.6.5 导出SQL Server数据表 第11章 SQL Server 2000数据库设计与操作 11.1 关系数据库 11.1.1 数据库及关系数据库概述 11.1.2 ...

    Visual C++ 数据库系统开发完全手册.part1

    10.6.2 导入SQL Server数据表 10.6.3 导入其他数据源的数据表 10.6.4 导出数据库 10.6.5 导出SQL Server数据表 第11章 SQL Server 2000数据库设计与操作 11.1 关系数据库 11.1.1 数据库及关系数据库概述 11.1.2 ...

    Oracle数据库管理员技术指南

    3.1 导出/导入特性概述 3.1.1 使用导出和导入 3.1.2 导出/导入的一般使用 3.1.3 导出方式 3.1.4 导入方式 3.1.5 创建必需的数据字典视图 3.1.6 指定导出/导入参数 3.1.7 在磁带上直接导出和导入数据 3.1.8 ...

Global site tag (gtag.js) - Google Analytics