/*--------------------建立oracle链接服务器 --------------------*/
EXEC sp_addlinkedserver
@server = 'ORCL', --ORCL是SQL中链接服务器名称
@srvproduct = 'Oracle', --Oracle 固定的
@provider = 'MSDAORA', --MSDAORA 固定的
@datasrc = 'ORCL' --DataSrc 本地服务名
GO
EXEC SP_ADDLINKEDSRVLOGIN 'ORCL', false, 'sa', 'SCOTT', 'admin'
--Sa是SQL本地登录帐号,POS/POS是ORACLE的登录帐号,但这句话对我们要达到的目的没有帮助。
--测试
SELECT * FROM ORCL..SCOTT.EMP --注意大写
/*---------------------建立sqlserver链接服务器------------------------------------*/
EXEC master.dbo.sp_addlinkedserver @server = N'192.168.0.170', @srvproduct=N'SQL Server'
GO
exec sp_addlinkedsrvlogin '192.168.0.170','false','sa','sa','123465'
go
SELECT * FROM [192.168.0.170].pubs.dbo.authors
--可以通过dts把表结构建好在目标oracle上
/*------------------------导入数据------------------------------------------------------*/
SELECT * FROM ORCL..SCOTT.zx_nr
insert ORCL..SCOTT.authors select * from authors
insert ORCL..SCOTT.employee select * from employee
insert ORCL..SCOTT.jobs select * from jobs
insert ORCL..SCOTT.stores select * from stores
insert ORCL..SCOTT.sales select * from [192.168.0.170].pubs.dbo.sales
insert ORCL..SCOTT.zx_xx select top 100 wj
from [192.168.0.170].vsatdata.dbo.zx_xx where lx='2'
insert ORCL..SCOTT.zx_nr select top 10 * from [192.168.0.170].vsatdata.dbo.zx_nr
where wj in (select top 100 wj from [192.168.0.170].vsatdata.dbo.zx_xx where lx='2')
select top 10 * from [192.168.0.170].vsatdata.dbo.zx_gp_nr
<style type="text/css">.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
</style>