通过创建SQLServer 2005到 Oracle10g 的链接服务器实现异构数据库数据转换方案
在图1中选中“链接服务器”，右键选择“新建链接服务器”，如图2，配置相关参数。
2、配置相关参数在“常规”选项中，填写“链接服务器名称”，访问接口选中
“ microsoft ole db provider for oracle”项。其中产品名称、数据源、访问接口字符串请填写配置的oracle客户端在本地配置的net服务名。如图3所示。
在“安全性”项中，选中使用此安全上下文建立连接，填写远程登录用户和密码，即登录远程oracle服务器的用户和密码。如图4所示。
3、异构数据库访问语句格式比如我们建立了链接服务器oradbconn，而oracle中在hrsoft用户下面建立了表webuser，那么我们的sql语句就是：
--清空oracle表中的数据
delete from oradbconn..hrsoft.webuser
--将sqlserver中的数据写到oracle中
insert into oradbconn.. hrsoft. webuser
select * from webuser
如果报告成功，那么我们的数据就已经写入到oracle中了。
用select * from oradbconn..hrsoft.webuser
查看oracle数据库中是否已经有数据了。
4、链接服务器应用a、查询oracle数据表方式一(这种方式，当oracle与sqlserver的数据类型不一致时经常报错,且速度稍慢)：
select * from [link2oracle]..[oracle_user_name].table_name;
我在执行该语句经常报类似错误信息：链接服务器 "link2oracle" 的 ole db 访问接口 "msdaora" 为列提供的元数据不一致。对象 ""cmcc"."os2_gis_cell"" 的列 "isopened" (编译时序号为 20)在编译时有 130 的 "dbtype"，但在运行时有 5。
b、查询oracle数据表方式二(经试验，这种方式使用起来很顺畅，不报错，且速度几乎和在oralce中一样快)：
select * from openquery(link2oracle,'select * from oracleusername.tablename')
您可以把openquery()当成表来使用。
c、举个例子（将oralce用户hrsoft下的用户表webuser导入到sqlserver2005数据库中）：
select * into os_gis_webuser from openquery(link2oracle,'select * from hrsoft. webuser)
d、更便捷的方式：通过创建同义词进行便捷查询:
create synonym os_gis_cell for [oraclelk]..[cmcc].os_gis_cell;
select * from os_gis_cell;
select * from os_gis_cell a where a.cellname is null;
注意：涉及 oracle 部分的 sql 语句，尤其是 [oraclelink]..[oracle_user_name].table_name 一定要大写，否则会报类似错误：
消息 7314，级别 16，状态 1，第 1 行
链接服务器 "oraclelk" 的 ole db 访问接口 "msdaora" 不包含表 ""cmcc"."os2_gis_cell""。该表不存在，或者当前用户没有访问该表的权限。
配置數據源：
工程inplan:
(description=(address_list=(address=(protocol=tcp)(host=192.168.2.6)(port=1521)))(connect_data=(sid=sa)(server=dedicated)))
erp:
(description=(address_list=(address=(protocol = tcp)(host = 192.168.3.25)(port = 1528)))(connect_data =(sid= tjtest)))
(description=(address_list=(address=(protocol = tcp)(host = 192.168.3.25)(port = 1528)))(connect_data =(sid= tjtest)))
新建作業里的代碼：
delete from xbld_information
insert into xbld_information(job_name, num_layers, jb_pp1, jb_pc1, jb_pp2, jb_pc2, jb_pp3, jb_pc3, pp_pp1, pp_pc1, pp_pp2, pp_pc2, pp_pp3, pp_pc3, pp_pp4,pp_pc4,bm)
select * from openquery(inplan,'
select mjs15.job_name, mjs15.num_layers,mjs1.mm1 jb_pp1,mjs1.mc1 jb_pc1,mjs2.mm2 jb_pp2,mjs2.mc2 jb_pc2,mjs3.mm3 jb_pp3,mjs3.mc3 jb_pc3,mjs11.mm1 pp_pp1,mjs11.mc1 pp_pc1,mjs12.mm2 pp_pp2,mjs12.mc2 pp_pc2,mjs13.mm3 pp_pp3,mjs13.mc3 pp_pc3,mjs14.mm4 pp_pp4,mjs14.mc4 pp_pc4,substr(mjs15.job_name,5,2) bm
from
(select mjl.job_name,
mjl.num_pcbs,
mjl.num_arrays,
mjl.num_panles_,
mjl.num_layers,
substr(mjl.op_size_y_string_,instr(mjl.op_size_y_string_,''='')+1,length(mjl.op_size_y_string_)-instr(mjl.op_size_y_string_,''='')+1) y_size, --祇へ糴
substr(mjl.op_size_x_string_,instr(mjl.op_size_x_string_,''='')+1,length(mjl.op_size_x_string_)-instr(mjl.op_size_x_string_,''='')+1) x_size --祇へ
from tj.my_job_list mjl
) mjs15,
(select *
from
(select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),1,material_name) mm1,
decode(dense_rank() over( partition by job_name order by material_name,material_count),1,material_count) mc1
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''core''
)where mm1 is not null
) mjs1,
(select *
from
(select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),2,material_name) mm2,
decode(dense_rank() over( partition by job_name order by material_name,material_count),2,material_count)mc2
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''core''
) where mm2 is not null
) mjs2,
(select *
from
(select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),3,material_name) mm3,
decode(dense_rank() over( partition by job_name order by material_name,material_count),3,material_count)mc3
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''core''
) where mm3 is not null
) mjs3,
(select *
from
( select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),1,material_name) mm1,
decode(dense_rank() over( partition by job_name order by material_name,material_count),1,material_count) mc1
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''prepreg''
)where mm1 is not null
) mjs11,
(select *
from (
select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),2,material_name) mm2,
decode(dense_rank() over( partition by job_name order by material_name,material_count),2,material_count)mc2
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''prepreg''
)where mm2 is not null
) mjs12,
(select *
from
( select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),3,material_name) mm3,
decode(dense_rank() over( partition by job_name order by material_name,material_count),3,material_count)mc3
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''prepreg''
)
where mm3 is not null
) mjs13,
(select distinct *
from
( select mjs.job_name,
decode(dense_rank() over( partition by job_name order by material_name,material_count),4,material_name) mm4,
decode(dense_rank() over( partition by job_name order by material_name,material_count),4,material_count)mc4
from tj.my_job_stackup_material_count mjs
where mjs.type_t=''prepreg''
) where mm4 is not null
) mjs14
where mjs15.job_name=mjs1.job_name(+)
and mjs15.job_name=mjs2.job_name(+)
and mjs15.job_name=mjs3.job_name(+)
and mjs15.job_name=mjs11.job_name(+)
and mjs15.job_name=mjs12.job_name(+)
and mjs15.job_name=mjs13.job_name(+)
and mjs15.job_name=mjs14.job_name(+)
')
update xbld_information set bm=b.describe from xbld_information a, xbld_bm b where a.bm=b.id