oracle sqlserver同歩,同步sqlserver数据到oracle数据库中例子

create table deptcode

(

id int identity(1,1) not null,

sno_cut varchar(20),

deptcode_card varchar(15),

deptcode_fee varchar(15)

)id primary

go

CREATE TABLE [idinfor] (

[no] [varchar] (20) COLLATE Chinese_PRC_CI_AS not NULL ,

[sno] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[name] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,

[sex] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,

[id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[address] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,

[mark1] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,

[mark2] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,

[deptcode_card] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,

[deptcode_fee] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,

[updatetimes] [tinyint] NULL ,

[no_card] [int] identity(1000000001,1) not NULL ,

[no_fee] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

[openflag] [char] (1),

[sexname] [char] (8)

) ON [PRIMARY]

GO

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

---创建其他库到中间库同步过程

ALTER proc p_process

as

--插入学工号新生成数据

insert into idinfor(no,sno,name,id,address,sexname)

select no,sno,name,id,substring(address,len(60),60),sex from srv1.dbo.idinformation b

where b.sno >'c1504' and not exists(

select * from idinfor where no=b.no)

--更新新生部门

update i set i.deptcode_card=c.deptcode_card,i.updatetimes=0

from idinfor i,depcode c

where substring(sno,0,6)=c.sno_cut and i.deptcode_card is null or substring(sno,0,3)=c.sno_cut and i.deptcode_card is null

--更新新生性别

update idinfor set sex=1

where sexname='男' and sex is null

update idinfor set sex=2

where sexname='女' and sex is null

---更新学工号，班级更改的数据

update i set i.sno=b.sno,i.updatetimes=i.updatetimes+1

from srv1.dbo.idinformation b,idinfor i

where b.no=i.no and i.mark1 is null and i.sno <> b.sno

update i set i.deptcode_card=c.deptcode_card,i.updatetimes=0

from idinfor i,depcode c

where substring(i.sno,0,6)=c.sno_cut and i.updatetimes >= 1

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

---创建中间库到一卡通同步过程

create proc t_process

as

insert into link_ora1..IDDBUSER.IDINFORMATION(no,name,sex,sno,deptcode,idno,addr,areacode,class,pidcode,born,idtype,indate,nationcode,peoplecode,zzmm,expdate,zip,checkflag,openflag,writeflag,openaccdate,LASTUPDATE,account,[right],jg,tel,email,enableid,temaccount,temflag,TEMDATETIME,EXPCARDFLAG)

select '00'+convert(char(12),no_card),rtrim(name),rtrim(sex),rtrim(sno),rtrim(deptcode_card),rtrim(id),rtrim(address),'001' ,'1' ,'01',null,'001',null,'156',null,null,null,null,'1','0',null,null,'',0,null,null,null,null,'2',0,'0',null,null from idinfor i

where not exists(select * from link_ora1..IDDBUSER.IDINFORMATION where no=i.no_card)

---更新开卡信息

update b set openflag=i.openflag

from idinfor b,link_ora1..IDDBUSER.I