分布式查询在异构数据库间数据导入导出中的应用

【摘要】(Faculty Mathematics and Computer Science, Hubei University,Wuhan 430062,China) Abstract:Firstly,introduced the basic theory of distributed query. Secondly, researched ...

摘要：首先介绍了分布式查询的基础理论，然后，结合具体实例，对如何使用Transact-SQL语句以及两个函数openRowSet和openDataSource在异构数据库间数据导入导出进行了仔细研究。

关键字：分布式查询；Transact-SQL语句；数据导入导出

中图分类号：TP311 文献标识码：A文章编号：1009-3044(2007)18-31511-03

Application of Distributed Query in Import and Export of Data in Isomerous DataBase

LV Shun-ying

(Faculty Mathematics and Computer Science, Hubei University,Wuhan 430062,China)

Abstract:Firstly,introduced the basic theory of distributed query. Secondly, researched that how to use the Transact-SQL statement and two functions as openRowSet and openDataSouce importsand exports data in isomerous database.

Key words:distributed query;Transact-SQL statement;import and export of data

1 引言

在实际工作中常常要对异构数据库间的数据进行导入导出工作，因此，不仅大多数数据库管理系统（DBMS）都会提供一定的系统级的数据导入导出功能，而且各种数据库应用系统也会根据具体需要提供灵活的应用级的数据导入导出功能。比如，SQL Server提供的数据转换服务(DTS)、命令行工具bcp、分布式查询都可以用于数据的导入导出。本文着重对MS SQL SERVER中分布式查询在异构数据库间数据导入导出中的应用加以研究与探讨。

2 分布式查询简介

2.1 分布式查询的理论基础

分布式查询支持SQL Server用户访问存储在多个SQL Server实例中的分布式数据和存储在各种可使用OLE DB提供程序访问的关系和非关系数据源中的异类数据，而这些数据可存储在相同或不同的计算机上。

Microsoft SQL Server 2000 通过使用 OLE DB（Microsoft 通用数据访问应用程序接口规范）支持分布式查询。OLE DB提供程序将数据表现在称为行集的表格格式对象中。MS SQL Server 2000允许在SELECT、INSERT、UPDATE 和 DELETE等Transact-SQL语句中象引用SQL Server表一样引用OLE DB行集。只要拥有数据的软件通过 OLE DB 提供程序在表格格式行集中表现其数据，就可在分布式查询中使用该数据。这种功能现在已内置到以OLE DB为外部数据接口的SQL Server中，其结构图如图1所示。

图1 MS SQLServe内置的OLE DB接口结构示意图

分布式查询通常通过配置链接服务器实现，但当引用 OLE DB 数据源的频率不够高，尚不足以保证配置链接服务器时，可使用特殊名称作为表引用，而使用OPENDATASOURCE和OPENROWSET函数可以提供特殊名称。

2.2 OPENDATASOURCE和OPENROWSET函数

(1)OPENDATASOURCE函数

仅当提供程序表现行集并使用四部分名称时，才可以使用 OPENDATASOURCE。该函数可以在能够使用链接服务器名的Transact-SQL语句中使用，并作为表或视图四部分名称的第一部分。四部分名称常用于分布式查询，格式为：linkedserver.catalog.schema.objectname，这四部分分别为分布式查询所

引用对象的链接服务器名、对象的目录名、对象的架构名和对象名或表名。使用OPENDATASOURCE函数需指定：用于访问数据源的OLE DB提供程序中登记为PROGID的名称和连接字符串――用以指定将各种连接属性传递到OLE DB提供程序。其语法为：OPENDATASOURCE(providerName,initString)。

基本使用方法示例：select * from opendataSource().catalog.schema.objectname。

(2)OPENROWSET函数

可以在任何返回行集的OLE DB提供程序中使用OPENROWSET函数，并且可以在 Transact -SQL语句中代替表或视图名称。使用OPENROWSET函数需指定：连接OLE DB数据源所需的全部信息和生成行集的对象名称或查询。其语法为：

OPENROWSET('providerName',{'dataSource';'userId';'password'|'providerString'}, {[catalog.][schema.]object|'query'})。

注意：其中的对象名是四部分名称中后三部分：catalog.schema.object。如果OLE DB提供程序在指定的数据源中支持多个目录和架构，那么就需要目录及架构名称。如果OLE DB提供程序不支持目录和架构，则可省略catalog及schema的值。如果提供程序只支持架构名，那么必须指定一个两部分名称，形式为schema.object。如果提供程序只支持目录名，那么必须指定一个三部分名称，形式为catalog.schema.object。

基本使用方法示例：select * from openrowset()。

3 分布式查询在导入导出数据中的应用

3.1 数据导入导出的基本思路

数据导入导出是个相对的概念，这里是相对Microsoft SQL Server而言。数据导入是指从Microsoft SQL Server的外部数据源中检索数据，并将数据插入到SQL Server表的过程。数据导出是指将SQL Server实例中的数据析取为某些用户指定格式的过程。

使用Transact-SQL语句从源数据表sourTableName把数据导到目的数据表destTableName的基本语句形式如下：

(1)目的表不存在时：SELECT * INTO destTableName FROM sourTableName

(2)目的表已存在时：INSERT INTO destTableName SELECT * FROM sourTableName

上述格式中，目的表的结构与源表相同，若目的表的结构与源表不同，则把其中的*号改为与目的表字段对应的源表字段名。若是更新数据，则使用UPDATE语句作类似处理。

对于异构数据库的数据导入导出，就是使用openrowset函数作为上述表名，或使用opendataSource函数作为上述四部分表名的第一部分即可。

3.2 数据导入导出的具体应用

本文为全文原貌 未安装PDF浏览器用户请先下载安装 原版全文

下面以把MS SQL SERVER2000数据库服务器hubu-swi-lsy上，数据库pubs中的 authors 表导出到不同数据库为例分别进行说明，数据导入的例子可以参考导出留给读者完成。

为叙述方便，假定下面用到的用户名为myName，密码为myPWD，导出时目的表为out_authors与authors表结构相同且已存在，导入时源表为out_authors，目的表为in_authors且不存在。下面有特殊说明的除外。

(1)导出到Oracle数据库或从Oracle数据库导入

使用“用于Oracle的Microsoft OLE DB提供程序”,providerName为MSDAORA，dataSource为用于Oracle数据库的SQL*Net别名（假设为Ora_net_Alias）, 对于OPENROWSET，其第三个参数只能是一个查询query。

authors导出到out_authors(以下简称数据导出)：

INSERT INTO OpenDataSource('MSDAORA', 'Data Source=Ora_net_Alias;User ID=myName; Password=myPWD')..myName.out_authors SELECT * from authors

out_authors导入到新表in_authors(以下简称数据导入):

SELECT * INTO in_authors FROM OPENROWSET('MSDAORA','Ora_net_Alias';'myName';'myPWD', 'SELECT * FROM out_authors')

注意：①在使用OpenDataSource函数时，要遵循以下规则：

如果在 Oracle 中创建的表名和列名没有引用的标识符，则全部使用大写字母名称。

如果在 Oracle 中创建的表名和列名有引用的标识符，则全部使用与 Oracle 中创建名称时相同的字母大小写格式。

②导入ORACLE数据库时目的表必须存在。

③导入导出时使用这两个函数中的哪一个都可以（下同）。

(2)导出到另一个SQL Server数据库或从另一个SQL Server数据库导入

使用“用于SQL Server的Microsoft OLE DB提供程序”,providerName为SQLOLEDB，dataSource为另一个SQL Server数据库（远程SQL Server数据库）的名称或服务器的名称，对于OPENROWSET，其第三个参数只能是一个查询query。

设远程数据库服务器名为remote_server。

数据导出：

INSERT INTO OpenDataSource('SQLOLEDB','Data Source=remote_server;User ID=myName; Password=myPWD').pubs.dbo.out_authors SELECT * from authors

数据导入：

SELECT * INTO in_authors FROM OPENROWSET('SQLOLEDB','remote_server';'myName';'myPWD', 'SELECT * FROM out_authors ORDER BY au_lname, au_fname')

(3)导出到ACCESS数据库或从ACCESS数据库导入

使用“用于Jet的Microsoft OLE DB提供程序”，providerName为Microsoft.Jet.OLEDB.4.0，dataSource为Access数据库文件（扩展文件名为.mdb）的完整路径名。Access数据库文件必须驻留在服务器上，dataSource在服务器上而不是在客户机上进行评估，路径必须是服务器上的有效路径，设为C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\northwind.mdb。默认情况下，用户名为Admin，密码为空。

注意：Access 数据库没有目录和架构名称。因此，Access数据库中的表要使用形如server...tablename 的四部分名称进行引用。

数据导入（northwind.mdb数据库中“产品”表导入到新表“产品1”）：

SELECT * INTO 产品1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\northwind.mdb';'admin';'',产品)

数据导出（产品1表导出到northwind.mdb数据库的已存在的表“产品1”）：

INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\northwind.mdb"')...产品1 SELECT * from 产品1

(4)导出到Excel电子表格或从Excel电子表格导入

使用“用于Jet的Microsoft OLE DB提供程序”，providerName为Microsoft.Jet.OLEDB.4.0，对于OPENROWSET函数只能使用providerString，而对于OpenDataSource 既可以使用providerString，也可以使用'dataSource; user_id; password'。dataSource为excel数据文件（扩展文件名为.xls）的完整路径名（假设有C:\ out_authors.xls），默认情况下，用户名为Admin，密码为空。

若把整个sheet页作为数据表，可以使用SHEET页名称作为表名，若把sheet页中的某些单元范围作为数据表则要使用与该范围对应的范围名称作为表名。

数据导出：

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=0; DATABASE=C:\out_authors.xls')...[sheet1$] SELECT *from authors

注意：①out_authors.xls必须先建立，并且sheet1页中也要指定列名。

② providerString中的HDR=YES或没有该项表示有标题行（即第一行作为字段名对待），字段顺序不变；HDR=NO表示没有标题行。IMEX=0是导入模式，IMEX=1是导出入模式，IMEX=2是更新模式。若HDR=NO;IMEX=0时字段顺序也不变；若HDR=NO而没有IMEX=0时，字段顺序会按照字典顺序重新排列。若IMEX=1时，则不能写入。

数据导入：

SELECT * INTO in_authors FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=NO;IMEX=1;DATABASE=C:\out_authors.xls',sheet1$)

本文为全文原貌 未安装PDF浏览器用户请先下载安装 原版全文

注意：HDR=NO时若IMEX=1则混合型字段转换为文本字段，且字段顺序不变，若IMEX=0，则字段顺序会按照字典顺序重新排列，并且字段类型不会转变为文本。HDR=YES时字段顺序不变。

(5)导出到文本文件或从格式化文本文件导入

使用“用于Jet的Microsoft OLE DB提供程序”，providerName为Microsoft.Jet.OLEDB.4.0，对于OPENROWSET函数只能使用providerString，而对于OpenDataSource 既可以使用providerString，也可以使用'dataSource; user_id; password'。dataSource为文本文件（扩展文件名为.txt）的完整路径名（假设有C:\ out_authors.txt），默认情况下，用户名为Admin，密码为空。

数据导出：

INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;HDR=NO;DATABASE=C:\', out_authors#txt) SELECT * from authors

注意：①必须先建立文件out_authors.txt，并且也要指定列名,列名间使用逗号分隔。

②HDR=NO时IMEX=0或IMEX=1或没有该选项时字段顺序不变；若HDR=YES或没有该选项时，则字段顺序会按照字典顺序重新排列。

③INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Data Source=c:

User ID=Admin;Password=;Extended properties=Text;')...[out_authors#txt] SELECT * from authors导出的字段顺序也按照字典顺序重新排列了。

数据导入：

SELECT * INTO in_authors FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','text;HDR=YES; DATABASE=C:\') ...out_authors#txt

注意：HDR=YES 或没有该项时字段顺序按照字典顺序重新排列；若HDR=NO则表示没有标题行，字段顺序不变。

(6)导出到dBase数据表或从dBase数据表导入

使用“用于Jet的Microsoft OLE DB提供程序”，providerName为Microsoft.Jet.OLEDB.4.0，dataSource为dBase数据文件（扩展文件名为.dbf）的完整路径名。

数据导入(C:\TEST.DBF导入到新表TEST1)：

SELECT * INTO TEST1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','dBase IV;DATABASE= C:\', 'select * from TEST.DBF')

数据导出(TEST1导出到C:\TEST1.DBF，TEST1.DBF与TEST1结构同，且已存在)：

INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase IV;DATABASE=C:\ ','select * from

TEST1.dbf') SELECT * from TEST1

(7)从FOXPRO数据表导入

把C:\Program Files\Microsoft Visual Studio\Vfp98\FOXUSER.DBF表导入到新表FOXUSER1中：

SELECT * INTO FOXUSER1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=C:\Program Files\Microsoft Visual Studio\Vfp98\','select * from FOXUSER.DBF')。

4 总结

本文详细给出了MS SQL SERVER与其他各种异构数据库间的数据表的导入导出。事实上，利用上述原理，还可以实现任意两个不同数据库之间的数据导入导出，例如Excel文件与文本文件之间，结合本文读者不难给出具体语句。

参考文献：

[1]王晓刚,杨春金.Excel到SQL Server数据库的数据导入导出技术研究[J].计算机与数字工程,2007(35),3.

[2]王长江,屈红刚等.利用ADO实现数据导入导出的关键技术[J].重庆科技学院学报：自然科学版,2006(8),2.'

注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。

本文为全文原貌 未安装PDF浏览器用户请先下载安装 原版全文