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

[happypolo]

问题描述:

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的修改\删除操作.

有做过类似的给点意见及思路????

[happypolo]

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

[happypolo]

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

[happypolo]

[Quote=引用 4 楼 dawuguj 的回复:]
如果怕性能问题，应该用临时表


SQL codeSELECT *
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.bid

delete tableA
where not exists (
select 1 from #t as t
where tableA.bid = t.bid
)

insert tableA
SELECT …
[/Quote]

---------------------

感谢老大: 但还有个问题是:

1. 是不是要整个试图每次都要遍历一遍视图及tableA ,oracle视图是出库流水帐明细表啊,可能数据量会很大???
该怎么操作???

2. 临时表#t 是不是要先在sqlserver 中建好,还是直接 insert into 会自动建立????

[happyflvstone]

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

[happyflvstone]

2、3、4楼和我同一个人

[happypolo]

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

[happyflvstone]

[Quote=引用 10 楼 happypolo 的回复:]
老大:
有没有增量同步比较好的方案???
象 sqlserver到sqlserver 的复制订阅方案~~~~~~~~
[/Quote]
你的情况应该可以配置一个事务复制

[happypolo]

不然,我只能比较最近时间段内的数据表是否一致了,还有这个时间段是否可以定到1年的时间长度(可能有2-3w条记录);
这种效率会不会太低???

[happyflvstone]

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

[happypolo]

[Quote=引用 11 楼 happyflvstone 的回复:]
引用 10 楼 happypolo 的回复:
老大:
有没有增量同步比较好的方案???
象 sqlserver到sqlserver 的复制订阅方案~~~~~~~~

你的情况应该可以配置一个事务复制
[/Quote]


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

[happypolo]

我是要求 oracle to sqlserver啊?

[happyflvstone]

[Quote=引用 12 楼 happypolo 的回复:]
不然,我只能比较最近时间段内的数据表是否一致了,还有这个时间段是否可以定到1年的时间长度(可能有2-3w条记录);
这种效率会不会太低???
[/Quote]
如果有最后修改时间字段

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

[happyflvstone]

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

[happypolo]

在sqlserver 中配置吗?

[you_tube]

[Quote=引用 18 楼 happypolo 的回复:]
在sqlserver 中配置吗?
[/Quote]
不是用oracle 里的工具

[nzperfect]

也就是说你对oracle的操作只能是select
并且oracle的这个视图数据还在不停的变化

在sql server端你建的这个表的目的是什么？

[dawuguj]

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
   )

[dawuguj]

但无法处理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.bid

delete 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
   )

[dawuguj]

如果怕性能问题，应该用临时表

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.bid

delete 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

[山书生]

厉害&……！