SQL Server 中导入导出数据三方法比较
作者:gaoyuan 发布时间:2009-01-21 14:22:00
当我们建立一个数据库时,并且想将分散在各处的不同类型的数据库分类汇总在这个新建的数据库中时,尤其是在进行数据检验、净化和转换时,将会面临很大的挑战。幸好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')...
table1
OPENROWSET相当于一个记录集,可以将直接当成一个表或视图使用。
在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 -S"GNETDATA/GNETDATA" -U"sa" -P"password"
BCP不仅能够通过命令行执行,同时也可以通过SQL执行,这需要一个系统存储过程xp_cmdshell来实现,如上面的命令可改写为如下形式。
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转换成目标记录中“已婚”或“未婚”。
Function Main()
DTSDestination("姓名") = DTSSource("姓名")
DTSDestination("年龄") = DTSSource("年龄")
If DTSDestination("婚姻状况") = 1 Then
DTSDestination("婚姻状况") = "已婚"
Else
DTSDestination("婚姻状况") = "未婚"
End If
Main = DTSTransformStat_OK
End 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时对数据进行快速导入导出的场合。
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 今天需要把一个省外项目的数据库从服务器上备份、拷贝到本机(跨地域传输数据库备份文件)。连上VPN,通过远程桌面连接,连接上服务器,发现数据库
- 原问题是这样的:如何用SQL语句(不是Oracle),求出下表每一行的5个字段中的最大值,最后生成一个新字段。例如:第一行最大值 -5.0
- 问题背景在开始正文之前,感谢用户名为怜索的朋友送给了我的博客2021年的第一个赞!import numpy as npimport matp
- 一、算法概述AdaBoost 是英文 Adaptive Boosting(自适应增强)的缩写,由 Yoav Freund 和Robert S
- 一、前言卷起来好吧,元旦已经过了,就开始写文章模式了。这篇文章会对完全数的各种侦测进行详细解释。写作不易,支持一波~二、完全数是什么1、定义
- 随着现在宽屏显示器的流行,Flash的全屏模式下,越来越需要考虑到普屏显示器与宽屏显示器的差别。Flash全屏模式有以下特点:窗口最大化,且
- 那就废话不多说,直接上代码吧!new_array = np.zeros((107,4))# 共107行 每行4列 初值为0>>&
- 我们日常用CSS布局的时候,关于图片背景,大部分的人都是一个背景一张图片的,怎么说呢?这是很标准的方法,但是这种普通制作方式下要保存大量图片
- 目的:在help(模块名)时,能够看见文件里面的注释。首先,在文件的最开头,如果有个多行注释(三引号),就会将注释写入__DOC__变量,在
- python的set和其他语言类似, 是一个无序不重复元素集, 基本功能包括关系测试和消除重复元素. 集合对象还支持union(联合), i
- 1、命令行工具概述日常命令行操作,相对应的众多命令行工具是提高生产力的必备工具,鼠标能够让用户更容易上手,降低用户学习成本。 而对于开发者,
- 本文实例讲述了PHP实现无限极分类的两种方式。分享给大家供大家参考,具体如下:面试的时候被问到无限极分类的设计和实现,比较常见的做法是在建表
- 一、说在前面 需求:有一张长为960,宽为96的图片,需要将其分割成10张96*96的图
- 前言:平时根据需求写代码.人工进行测试往往不会面面俱到,还会因为需求的改变繁琐的进行测试通过完成一个测试函数,可以大大简化测试的步骤,并且在
- 项目中需要实现人脸登陆功能,实现思路为在前端检测人脸,把人脸照片发送到后端识别,返回用户token登陆成功前端调用摄像头使用tracking
- 最近做项目正好需要坐标的转换各地图API坐标系统比较与转换;WGS84坐标系:即地球坐标系,国际上通用的坐标系。设备一般包含GPS芯片或者北
- 本文实例讲述了php实现的微信分享到朋友圈并记录分享次数功能。分享给大家供大家参考,具体如下:1.引入JS文件2.通过config接口注入权
- 单例模式是一种常用的软件设计模式。在它的核心结构中只包含一个被称为单例类的特殊类。通过单例模式可以保证系统中一个类只有一个实例而且该实例易于
- 1 plot 函数语法plot:绘制二维线图NO.1 绘制横轴为X,竖轴为Y二维线图,Y值与X值一一对应。plot(X,Y)如果 X 和 Y
- WHERE 条件有时候操作数据库时,只操作一些有条件限制的数据,这时可以在SQL语句中添加WHERE子句来规定数据操作的条件。语法:SELE