【SQL Server数据迁移】链接服务器、分布式查询：SQL Server、ORACLE、Excel、txt、Access
链接服务器为SQL Server提供了从远程数据源访问数据的能力，可以执行查询、修改数据、远程过程调用。远程数据源可以是同类的(数据源可以是另一个SQL Server实例)，也可以是不同类的(其他关系型数据库产品和数据源，如：DB2，ORACLE，EXCEL，ACCESS，文本文件)。
使用跨平台联接查询可以访问遗留数据库，而且不需要对既有的数据源进行合并、迁移。
分布式查询可以引用一个或多个链接服务器进行查询，在远程表、视图、存储过程上执行读取和修改操作。链接服务器支持的查询类型取决于OLE DB访问接口提供的事务支持级别。
可以通过在from子句中使用4部分组成的远程对象名称来引用链接服务器，或者使用T-SQL命令openquery来运行分布式查询。
本文主要讲创建到5种数据源的链接服务器，还有bcp的方式来导入导出数据，还有openrowset的特殊用法：
1、SQL Server
2、ORACLE
3、Excel
4、txt
5、Access
6、bcp方式导入导出数据
7、openrowset来执行存储过程
1、SQL Server
--修改高级参数
sp_configure \'show advanced options\',1
go
--允许即席分布式查询
sp_configure \'Ad Hoc Distributed Queries\',1
go
--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖
reconfigure with override
go
/*=============================================================
在分布式即席查询中，
select into语句不能用于把本地的表导出到远程服务器
解决办法：
要在远程服务器新建表，必须通过execute at来执行DDL语句，
而要执行execute at，必须先设置链接服务器的rpc、rpc out属性为true，
最后用insert into把本地的数据导入到远程
===============================================================*/
--1、链接服务器为SQL Server
--1.1.1建立链接服务器,通过@datasrc指定数据源，适合链接其他多种数据库
EXEC master.dbo.sp_addlinkedserver @server = N\'Link\',
@srvproduct=\'ms\',
@provider=N\'SQLNCLI\',
@datasrc=N\'192.168.1.5,1433\'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N\'Link\',
@useself=N\'False\',
@locallogin=NULL,
@rmtuser=N\'sa\',
@rmtpassword=\'xyz\'
--1.1.2建立链接服务器,通过@server参数指定服务器，适合直接连接sql server
EXEC master.dbo.sp_addlinkedserver @server = N\'192.168.1.5,1433\',
@srvproduct=N\'SQL Server\'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N\'192.168.1.5,1433\',
@useself=N\'False\',
@locallogin=NULL,
@rmtuser=N\'sa\',
@rmtpassword=\'xyz\'
--1.1.3设置链接服务器的属性，从而远程调用存储过程rpc
EXEC master.dbo.sp_serveroption @server=N\'192.168.1.5,1433\',
@optname=N\'rpc out\',
@optvalue=N\'true\'
EXEC master.dbo.sp_serveroption @server=N\'192.168.1.5,1433\',
@optname=N\'rpc\',
@optvalue=N\'true\'
--1.1.4.1通过链接服务器来执行分布式查询
select * from link.wc.dbo.wcc
select * from [192.168.1.5,1433].wc.dbo.wcc
--1.1.4.2通过openquery来执行分布式查询,需要先建立链接服务器
select *
from openquery([link],\'select * from wc.dbo.wcc\')
--1.1.5查看链接服务器
select server_id,
name,
product,
provider,
data_source,
is_linked,
is_remote_login_enabled,
is_remote_proc_transaction_promotion_enabled,
lazy_schema_validation
from sys.servers
--查看链接登录名
select s.name,
ll.remote_name,
sp.name
from sys.linked_logins ll
inner join sys.servers s
on s.server_id = ll.server_id
left join sys.server_principals sp
on sp.principal_id = ll.local_principal_id
--1.1.6删除链接服务器登录名映射、删除链接服务器
exec sp_droplinkedsrvlogin @rmtsrvname = \'192.168.1.5,1433\',
@locallogin = null
exec sp_dropserver @server = \'192.168.1.5,1433\'
exec sp_droplinkedsrvlogin @rmtsrvname = \'link\',
@locallogin = null
exec sp_dropserver @server = \'link\'
--1.2用openrowset函数
--1.2.1第一种写法
SELECT *
FROM
OPENROWSET(\'SQLOLEDB\',
\'server=192.168.1.5,1433;u, --字符串
wc.dbo.wcc) --直接写表的名称
--1.2.2第二种写法
SELECT *
FROM
OPENROWSET(\'SQLOLEDB\',
\'server=192.168.1.5,1433;
uid=sa;
pwd=xyz\', --字符串
\'select * from wc.dbo.wcc
where wcc_name =\'\'abc\'\'\') --查询语句,注意不能用字符串连接符
--1.3用opendatasource函数
select *
from
opendatasource(\'SQLOLEDB\',
\'DATA SOURCE=192.168.1.5,1433;
USER ID=sa;
password=xyz\').wc.dbo.wcc
2、Oracle
--================================================================
--2、链接服务器为Oracle,使用oracle的oledb库MSDAORA
--2.1.1为建立链接服务器
EXEC sp_addlinkedserver
@server = \'OracleLinkedServer\', --要创建的链接服务器名称
@srvproduct = \'Oracle\', --产品名称
@provider = \'MSDAORA\', --OLE DB 字符
@datasrc = \'orcl\' --数据源
--2.1.2为创建连接服务器登陆
EXEC sp_addlinkedsrvlogin
@rmtsrvname = \'OracleLinkedServer\', --链接服务器名称
@useself = \'false\',
@locallogin = NULL,
@rmtuser = \'scott\', --Oracle服务器的登陆用户名
@rmtpassword = \'tiger\' --Oracle服务器的登陆密码
--2.2为设置链接服务器属性
exec sp_serveroption @server=\'OracleLinkedServer\',
@optname = \'rpc\', --从给定的服务器启动rpc
@optvalue =\'true\' --这样可以调用远程的存储过程
exec sp_serveroption @server=\'OracleLinkedServer\',
@optname = \'rpc out\', --对给定的服务器启动rpc
@optvalue =\'true\' --在远程执行DDL语句
--2.3为在远程服务器上执行DDL语句新建表，用oracle的方式书写语句
exec(\'create table SCOTT.EMPXX(iddd int)\') at OracleLinkedServer
--2.4用openrowset函数，把oracle的数据直接新建表的同时插入数据
select *
INTO EMP_X
from openrowset(\'MSDAORA\',\'orcl\';\'scott\';\'tiger\',SCOTT.EMP)
select *
INTO EMP_X
from openrowset(\'MSDAORA\',\'orcl\';\'scott\';\'tiger\',\'select * from SCOTT.EMP\')
--2.5为opendatasource函数，查询一下oracle中的数据
select *
from
opendatasource(\'MSDAORA\',\'Data Source=orcl;
User ID=scott;
password=tiger\')..SCOTT.EMP
3、Excel
/*==================================================================
1.很难从显示的错来知道：openrowset,opendatasource这两个函数哪儿出错了。
2.必须保证函数中的参数正确，否则会有莫名其妙的错误。
3.由于导入的Excel文件格式只能是.xls，所以在导入数据时，
可以先把要导入的扩展名为.xlsx的文件另存为扩展名.xls的文件，
然后把原始.xlsx数据，在格式不变的情况下复制到新的.xls。
4.在导入数据时，必须要按照目标表的字段类型、长度，来转化excel中的数据，
比如，那么在导入时把float转化成numeric，然后再转成varchar，
要是直接从float转成varchar，会把有些数转化成用科学计数法显示的数字，
再进一步转化是会报错。
5.导入到远程的目标表时，目标表必须已经存在
========================================================================*/
--3.Excel
--3.1.1链接服务器excel,不用建立服务器登录名
EXEC sp_addlinkedserver
@server = \'EXCEL\',
@srvproduct = \'Jet 4.0\',
@provider = \'Microsoft.Jet.OLEDB.4.0\',
@datasrc = \'c:\t.xls\',
--@location = NULL,
@provstr = \'Excel 5.0;\' --不加分号也可以
--3.1.2通过链接服务器查询
SELECT *
FROM [EXCEL]...[sheet1$]
--3.1.3引用12.0库
EXEC sp_addlinkedserver
@server = \'EXCEL\',
@srvproduct = \'Jet 12.0\',
@provider = \'microsoft.ace.oledb.12.0\',
@datasrc = \'c:\t.xls\',
--@location = NULL,
@provstr = \'Excel 12.0;\' --不加分号也可以
SELECT *
FROM [EXCEL]...[sheet1$] - -查询语句完全一样
--3.2.1用openrowset函数,引用4.0的库
select *
from
openrowset(\'microsoft.jet.oledb.4.0\',
\'Excel 5.0;database=c:\t2.xls\', --文件必须存在
sheet1$) --表必须存在
select *
from
openrowset(\'microsoft.jet.oledb.4.0\',
\'Excel 5.0;database=c:\t2.xls\',
\'select * from [sheet1$]\')
/*=========================================================================
如果在要导入数据的目标机器上装有office2007及其以上版本，
那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0,
excel的版本为:12.0
注意：不能在openrowset中写任何的空格等多余的字符，
否则会报错，而且错误没有针对性
===========================================================================*/
--3.2.2用openrowset函数,引用4.0的库
select *
from openrowset(\'microsoft.ace.oledb.12.0\',
\'Excel 12.0;database=c:\test.xlsx\',
\'select * from [sheet1$]\')
--3.3.1用opendatasrouce,引用4.0的库
select *
from
opendatasource(\'microsoft.jet.oledb.4.0\',
\'data source=c:\t.xls;Extended Properties=Excel 5.0\')...[sheet1$]
--3.3.2用opendatasrouce,引用12.0的库
select *
from
opendatasource(\'microsoft.ace.oledb.12.0\',
\'data source=c:\t.xls;Extended Properties=Excel 12.0\')...[sheet1$]
4、txt
--===========================================================
--5.txt,cvs格式的数据导入
--5.1建立txt的连接服务器
EXEC sp_addlinkedserver
@server = \'txtsrv\',
@srvproduct = \'Jet 4.0\',
@provider = \'Microsoft.Jet.OLEDB.4.0\',
@datasrc = \'c:\\', --文件所在的目录
--NULL,
@provstr = \'Text\'
--设置登录名
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO
--列出在链接服务器上的表,会列出目录中所有的文件:t#txt , list#csv
EXEC sp_tables_ex txtsrv
GO
--查询
SELECT *
FROM txtsrv...[t#txt]
--5.2.1通过bulk insert的方式导入数据,必须先建立一个表
create table tt(a1 varchar(10),
a2 varchar(10),
a3 varchar(10))
--5.2.2大量插入,表的字段必须与要导入的文件中的字段数量一致
BULK INSERT tt
FROM \'c:\t.txt\' --扩展名为.csv的文件也可以
WITH(
FIELDTERMINATOR = \',\', --字段分隔符
ROWTERMINATOR = \'\n\') --行终止符
--5.2.3
SELECT * FROM tt
/*====================================================
5.3用逗号分隔字段，导入txt和csv格式的文件
在32位系统上通过openrowset函数，用Microsoft Text Driver或
OLE DB Provider for Jet库将txt和CSV数据导入SQL Server表，
但是没有大容量导入优化。
======================================================*/
--5.3.1csv格式，通过Microsoft Text Driver驱动
select *
from
openrowset(\'MSDASQL\',
\'Driver={Microsoft Text Driver (*.txt; *.csv)};
DBQ=C:\;\', --驱动程序,存放txt或cvs的文件路径
\'select * from list.csv\') --用文件名称做为表名
--5.3.2txt格式，通过Microsoft Text Driver驱动
select *
from
openrowset(\'MSDASQL\',
\'Driver={Microsoft Text Driver (*.txt; *.csv)};
DBQ=C:\;\', --驱动程序,存放txt或cvs的文件路径
\'select * from list.txt\')
/*===================================================
如果分隔符不是逗号那么需要定义schema.ini文件。
schema.ini要和数据文件放在同一个目录下面.
schema.ini的定义方法：
[t.txt]
COLNAMEHEADER=TRUE
FORMAT=csvdelimited
col1=name1 short
col2=name2 short
col3=name3 short
col4=name4 short
第一行:如果文本文件的数据有列名，并且作为查询结果的列名的话，
则ColNameHeader应该为true，否则定义为false，
但是如果后面定义了列名的话，则不使用文本文件中数据的列名，而使用后面定义的列名。
但是如果数据有列名的，最好定义为true，否则的话，数据的列名将作为表中的数据。
第二行:format定义数据的分隔符。
一般的分隔符：空格，制表符，逗号。
分别对应：delimited( )(注意括号里应该有一个空格),tabdelimited,csvdelimited。
在选择分隔符时，建议使用tab，因为数据看起来比较整齐，但不能为保证数据的严格整齐，
而在数据当中使用多个tab，这样会导致读取数据时出现错误。其他的逗号和空格同样。
第三行到最后:指定每一列的列名、数据类型、字符集(使用ansi或者oem)、数据类型转换。
jet数据类型：bit,byte,short,long,currency,
single,double,datetime,text,momo
ODBC数据类型：char,float,int,longchar,date.
后面两个如果没有特殊情况的话可以不写，默认即可。
==========================================================================*/
--5.3.3txt格式，通过OLE DB Provider for Jet
select *
from
openrowset(\'microsoft.jet.oledb.4.0\',
\'text;
hdr=yes;
database=c:\\', --文件的路径
t#txt) --格式为：文件名称#扩展名
--5.3.4csv格式，通过OLE DB Provider for Jet
select *
from
openrowset(\'microsoft.jet.oledb.4.0\',
\'text;
hdr=yes;
database=c:\\', --文件的路径
list#csv) --格式为：文件名称#扩展名
5、Access
--6.Access
--6.1建立连接服务器
exec sp_addlinkedserver
@server =\'ACCESSsrv\',
@srvproduct =\'Access\',
@provider = \'Microsoft.Jet.OLEDB.4.0\',
@datasrc = \'c:\t.mdb\'
/*===========================================
1.若要访问不安全的 Access 数据库，
尝试访问Access数据库的SQL Server登录名,
应将登录映射定义为用户名Admin，且不带密码:
exec sp_addlinkedsrvlogin
@rmtsrvname = \'ACCESSsrv\',
@userself = false,
@locallogin = \'Joe\',
@rmtuser = \'Admin\',
@rmtpassword = NULL
2.若要访问安全的Access数据库，请使用注册表编辑器配置注册表，
以使用Access所用的正确的工作组信息文件。
使用注册表编辑器,
把Access使用工作组信息文件的完整路径名添加到下面注册表项：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
配置完注册表项后，创建本地登录名到Access登录名的登录名映射：
EXEC sp_addlinkedsrvlogin
\'ACCESSsrv\',
false,
\'Joe\',
\'AccessUser\', --用户名
\'034fhd99dl\' --密码
===================================================================*/
SELECT *
FROM [ACCESSsrv]...wc
--6.2使用12.0库导入access数据
SELECT *
FROM
OPENROWSET(\'microsoft.ace.oledb.12.0\', --库名称
\'C:\t.accdb\';\'admin\';\'\', --\'路径和文件名称\';\'用户名\';\'密码\'
e); --表名
--6.3使用4.0库导入access数据
SELECT *
FROM
OPENROWSET(\'microsoft.jet.oledb.4.0\', --库名称
\'C:\t.mdb\';\'admin\';\'\', --\'路径和文件名称\';\'用户名\';\'密码\'
\'select * from wc\'); --表名
6、bcp
--4.通过bcp导入导出数据,通过openrowset导入数据
/*=========================================================
以下为bcp命令行常用的参数,注意大小写：
-c 以char作为存储类型
-w 和-c类似，只有当使用unicode字符集拷贝数据时使用nchar作为存储类型
-T 用可信连接
-S sql server服务器名称
in 导入数据
out 导出数据
queryout 查询导出数据
-U 用户名
-P 密码
-F 从哪一行开始导入
-L 直到哪一行结束
-f 格式文件
-x 与-f一起使用，用来生产xml格式的格式文件
-t 指定字段分隔符是"\t"
-r 指定行分隔符是"\n"
=====================================================*/
--导出格式化文件
--4.1.1用可信连接登陆
bcp wc.dbo.calendar format nul -f c:\calendar.fmt
-c -T -S pc0227gry\mssqlserver2008
--4.1.2用户名、密码登陆
bcp wc.dbo.calendar format nul -f c:\calendar.fmt
-c -Usa -Pxyz -S pc0227gry\mssqlserver2008
--4.2导出数据文件，可以把远处服务器的数据导出保存为本地的文件
bcp wc.dbo.calendar out c:\calendar.txt -f c:\calendar.fmt
-c -T -S PC0227GRY\MSSQLSERVER2008
--4.3导入数据
bcp wc.dbo.calendar in c:\calendar.txt -c -f c:\calendar.fmt
-Usa -Pxyz -S pc0227gry\mssqlserver2008
/*=============================================================
4.3openrowset函数带bulk操作符的用法
第1种用法：
openrowset
(
bulk 要加载结果集的文件名,
formatfile = 格式化文件名,
bulk选项
)
第2种用法：
openrowset
(
bulk 文件名,
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB
)
==============================================================*/
--4.4.1第1种用法,把bcp导出的数据，导入数据库中
--具体导入数据时，可以先用bcp把表的格式文件倒出来，然后在把数据导进去，这样按照格式自动转化
select *
from
openrowset(bulk \'c:\calendar.txt\', --要读取的文件路径和名称
formatfile=\'c:\calendar.fmt\', --格式化文件的路径和名称
firstrow = 1, --要载入的第一行
--lastrow = 1000, --要载入的最后一行,此值必须大于firstrow
maxerrors = 10, --在加载失败之前加载操作中最大的错误数
errorfile =\'c:\calendar_error.txt\', --存放错误的文件
rows_per_batch = 100 --每个批处理导入的行数
) as calendar
--4.4.2第2种用法
select 1,
BulkColumn --这个是从数据源获取的，要插入的列，默认的列名
from openrowset(
BULK \'c:\helloworld.class\',--指定文件路劲，这里是本地路径
SINGLE_BLOB --指定二进制数据是SINGLE_BLOB
--指定文本是SINGLE_CLOB、SINGLE_NCLOB
)X --必须要别名
select 1,
binColumn --引用后面定义的列别名
from openrowset(
BULK \'c:\Katongxiar01.jpg\', --指定文件路劲，这里是本地路径
SINGLE_BLOB --指定二进制数据是SINGLE_BLOB
--指定文本是SINGLE_CLOB、SINGLE_NCLOB
)X(binColumn) --这里还定义了列的别名
--可以这么更新
update t
set pic = (
select BulkColumn
from openrowset(bulk \'c:\dwhj.bmp\',
SINGLE_BLOB) AS X --一定不要忘了定义别名
select 1,
binColumn --引用后面定义的列别名
from openrowset(
BULK \'c:\calendar.txt\', --指定文件路劲，这里是本地路径
SINGLE_CLOB --指定二进制数据是SINGLE_BLOB
--指定文本是SINGLE_CLOB、SINGLE_NCLOB
)X(binColumn) --这里还定义了列的别名
7、通过openrowset来执行存储过程，但是存储过程不能带参数：
--可以执行存储过程
--在调用openrowset等函数，注意参数格式，以及参数之间的字符的格式。
SELECT *
FROM OPENROWSET(\'SQLOLEDB\',
\'Server=PC0627JVC\MSSQLSERVER2008;Trusted_Connection=yes;database=master\',
\'exec sp_lock\')
SELECT *
FROM OPENROWSET(\'SQLOLEDB\',
\'Server=PC0627JVC\MSSQLSERVER2008;u,
\'exec master.dbo.sp_lock\')
- 上一篇 »python分布式进程
- 下一篇 »详解5种Java中常见限流算法