如何实现oracle 的视图数据同步到sqlserver中

Question

问题描述:1.我开发sqlserver 数据库系统时,需要用oracle 数据库中的数据;oracle 数据库系统只提供了一个视图,就是说我只能进行视图的select 操作权限,没有其它任何oracle 的权限;
2.我使用oracle 的select 用户名和密码,通过
SELECT *
FROM OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ') 
可以查询出需要的数据;
3.我现在需要做的是将 Hync31.V_QY_CKLSZ 中的数据同步到 sqlserver 中我自己建的表 tableA 中,tableA 的数据结构包含Hync31.V_QY_CKLSZ中的所有数据项,同时还有新的数据项, 其间都有主键BID;
4.由于从oracle 同步到 sqlserver 后,原来oracle 中的数据可能存在修改\删除操作,因此对修改\删除数据也需要反映到 sqlserver 中.
4.因此要实现同步,只能在 sqlserver 端进行开发,并且只能是增量同步.
5.我目前做法是:
   在sqlserver 编程, 通过 insert into OPENROWSET where bid not in(tableA) 来实现,但无法处理oracle的修改\删除操作.有做过类似的给点意见及思路????

Answer 1

也就是说你对oracle的操作只能是select
并且oracle的这个视图数据还在不停的变化在sql server端你建的这个表的目的是什么？

Answer 2

insert tableA 
SELECT *,其他字段
FROM OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ') as t
where not exists (
   select 1 from tableA 
   where bid = t.bid
   )

Answer 3

但无法处理oracle的修改\删除操作. 
加两个语句修改、删除update tableA  set
   col1 = t.col1
   ,col2=t.col2
   ,...
from tableA,OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ') as t
where tableA.bid = t.biddelete tableA
where not exists (
  select 1 from OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ') as t
  where tableA.bid = t.bid
  )insert tableA 
SELECT *,其他字段
FROM OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ') as t
where not exists (
   select 1 from tableA 
   where bid = t.bid
   )

Answer 4

如果怕性能问题，应该用临时表SELECT * 
INTO #t
FROM OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ') 
update tableA  set
   col1 = t.col1
   ,col2=t.col2
   ,...
from tableA,#t as t
where tableA.bid = t.biddelete tableA
where not exists (
  select 1 from #t as t
  where tableA.bid = t.bid
  )insert tableA 
SELECT *,其他字段
FROM #t as t
where not exists (
   select 1 from tableA 
   where bid = t.bid
   )
drop table #t

Answer 5

我为了实现同不啊
然后其它应用就只对sqlserver 操作就行了

Answer 6

我为了实现同步啊 
然后其它应用就只对sqlserver 操作就行了

Answer 7

---------------------感谢老大: 但还有个问题是: 1.  是不是要整个试图每次都要遍历一遍视图及tableA ,oracle视图是出库流水帐明细表啊,可能数据量会很大???
该怎么操作???2.   临时表#t 是不是要先在sqlserver 中建好,还是直接 insert into 会自动建立????

Answer 8

1.数据量大确实是个问题，最好在oracle加多个字段，比如最后修改时间之类的，这样你每次同步只取出某个时间后的数据，但是因为数据有删除，万一oracle那边删除了几年前的数据，就可能同步不了
2、SELECT * INTO #t from ...会自动产生临时表

Answer 9

老大:
有没有增量同步比较好的方案???
象 sqlserver到sqlserver 的复制订阅方案~~~~~~~~ 

Answer 10

如果是sqlserver到sqlserver 
事务复制的配置应该相当简单，特别是对于sql2005或者以上，效果也可以，对性能有部分影响

Answer 11

???? 配置一个事务复制,  是sqlserver中配置吗 ??? 

Answer 12

我是要求 oracle to sqlserver啊?

Answer 13

如果有最后修改时间字段SELECT * 
INTO #t 
FROM OPENROWSET('MSDAORA.1','hydata';'qyselect';'qy08select12','SELECT * FROM Hync31.V_QY_CKLSZ where 最后修改时间字段>=''2008-12-1''')
update tableA  set 
  col1 = t.col1 
  ,col2=t.col2 
  ,... 
from tableA,#t as t 
where tableA.bid = t.bid delete tableA 
where 最后修改时间字段>='2008-12-1'
and not exists ( 
  select 1 from #t as t 
  where tableA.bid = t.bid 
  ) insert tableA 
SELECT *,其他字段
FROM #t as t
where not exists (
   select 1 from tableA 
   where bid = t.bid
   )
drop table #t

Answer 14

oracle to sqlserver复制是有的，不过我没配置过，不知道有没有特殊的难题