两个数据库之间的数据传输

[skyman2000]

CREATE PROCEDURE [dbo].[HA_HRTA_DATA_UPDATE_PRO] AS
--此存储过程可以满足多台SQL Server向考勤系统的ORACLE数据库中正确存储打卡数据
--从oracle 更新人事资料表到 sql server ?部门能否为空，部门长度不够

declare
@card_id varchar(10),
@emp_no varchar(15),
@emp_sum int,
@nFlag int

--建立一个临时#record_temp表，把前一天的打卡记录存进去

if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb..#record_temp'))
drop table #record_temp


select * into #record_temp from record where convert(char(10),kqdate,102)=convert(char(10),getdate()-1,102)

--从sql server库的Record表的前一天的打卡记录更新到oracle库的a_origin_ta_info表中
--删除oracle中前一天的记录

delete from openquery(hrtalink,'select CARD_ID,VRF_TIME,TERMINAL_ID,CRT_WAY,CRT_GUARD from A_ORIGIN_TA_INFO')
where convert(char(10),VRF_TIME,102)=convert(char(10),getdate()-1,102) and CRT_WAY='0'


--插入oracle中前一天的记录
--从sql server更新发卡记录到oracle

insert into openquery(hrtalink,'select CARD_ID,VRF_TIME,TERMINAL_ID,CRT_WAY,CRT_GUARD from A_ORIGIN_TA_INFO')
select distinct ic_no,
convert(datetime,substring(d_time,1,4)+'-'+substring(d_time,5,2)+'-'+substring(d_time,7,2)+' '+time1+':'+right('000'+ltrim(rtrim(t_sec)),2)+'.000')
,right('00000'+ltrim(rtrim(pos_no)),5),'0',''
from #record_temp

--将SQL Server库的record表的前一天的打卡记录总数累计到oracle库的a_ta_origin_sum表中
--当日记录不存在，插入
--当日记录已存在，把当前值累加到库中

declare emp_cur1 cursor for
select count(*) from #record_temp

open emp_cur1
fetch next from emp_cur1 into @emp_sum
close emp_cur1
deallocate emp_cur1

declare emp_cur1 cursor for
select count(*) from openquery(hrtalink,'select CALC_DATE,EMP_SUM,MEMO,status from A_TA_ORIGIN_SUM')
where convert(char(10),CALC_DATE,102)=convert(char(10),getdate(),102)

open emp_cur1
fetch next from emp_cur1 into @nFlag
close emp_cur1
deallocate emp_cur1

--当日记录不存在，插入
if (@nFlag = 0 )
insert into openquery(hrtalink,'select CALC_DATE,EMP_SUM,MEMO,status from A_TA_ORIGIN_SUM')
values(getdate(),@emp_sum,'正常计算','0')

--当日记录已存在，把当前值累加到库中
else
Update openquery(hrtalink,'select CALC_DATE,EMP_SUM,MEMO,status from A_TA_ORIGIN_SUM')
set emp_sum=@emp_sum + emp_sum
where convert(char(10),CALC_DATE,102)=convert(char(10),getdate(),102)


--根据SQL Server库的record表的前一天的打卡记录，分别在本SQL Server中查找Card_id和EmpNo的对应关系
--更新Oracle库的a_emp_card_map表
--a_emp_card_map 中无此EmpNo和Card_id对应关系的，插入（关联emp_info表的emp_name, dept等字段）
--a_emp_card_map 中有此EmpNo, 但Card_id不同的，更新Card_id


--先建立前一天的记录的emp_no和card_id的唯一对应关系临时表
if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb..#peoplecard_temp'))
drop table #peoplecard_temp


Select distinct emp_no, ic_no into #peoplecard_temp from #record_temp

--再建立同时存在于#peoplecard_temp 和oracle中的a_emp_card_map的记录表
if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb..#people_temp'))
drop table #people_temp


Create table #people_temp(
emp_no varchar(15),
ic_no varchar(10)
)

Insert into #people_temp
Select a.emp_no, a.ic_no from #peoplecard_temp as a, openquery(hrtalink,'select empno,emp_name,card_id,status,memo from a_emp_card_map') as b
where a.emp_no = b.empno and a.ic_no = b.card_id


--接着，在#peoplecard_temp中找出不属于#people_temp表的记录，放在#peoplenotexist_temp中
if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb..#peoplenotexist_temp'))
drop table #peoplenotexist_temp

Select a.emp_no, a.ic_no into #peoplenotexist_temp from #peoplecard_temp as a, #people_temp as b where a.emp_no<>b.emp_no

--处理#peoplenotexist_temp中的记录
declare people_cursor cursor for
select emp_no, ic_no from #peoplenotexist_temp

open people_cursor
fetch next from people_cursor into @emp_no, @card_id
while @@fetch_status =0
begin

declare findpeople_cursor cursor for
select count(*) from openquery(hrtalink,'select empno,emp_name,card_id,status,memo from a_emp_card_map')
where empno=@emp_no

open findpeople_cursor
fetch next from findpeople_cursor into @nFlag
close findpeople_cursor
deallocate findpeople_cursor

--在oracle的a_emp_card_map中找出@emp_no的记录，如果@nFlag=0 表示没有，就添加到a_emp_card_map中
if (@nFlag = 0)

Insert into openquery(hrtalink,'select empno,emp_name,card_id,status,memo from a_emp_card_map')
select a.emp_no,
b.emp_name COLLATE Chinese_PRC_CI_AS,
a.ic_no,
case when left(a.emp_no,1)='E' then '1'
when left(a.emp_no,1)='G' then '3'
when left(a.emp_no,1)='C' then '4'
else '9' end ,
b.dept_name COLLATE Chinese_PRC_CI_AS
from #peoplenotexist_temp as a, OPENQUERY(hrtalink, 'SELECT empno,emp_name,deptno,dept_name,sex FROM emp_info' ) as b
where a.emp_no=@emp_no and b.empno=@emp_no

-- exist data, so update it
else
Update openquery(hrtalink,'select empno,emp_name,card_id,status,memo from a_emp_card_map')
set card_id=@card_id
where empno=@emp_no

fetch next from people_cursor into @emp_no, @card_id
end
close people_cursor
deallocate people_cursor


--将SQL Server库的ICLost表中的挂失卡的打卡记录，从Oracle库的a_emp_card_map表中删掉
declare IClost_cursor cursor for
select 卡号 COLLATE Chinese_PRC_CI_AS from IClost

open IClost_cursor
fetch next from IClost_cursor into @card_id
while @@fetch_status =0
begin
delete from openquery(hrtalink,'select card_id from a_emp_card_map') where card_id=@card_id COLLATE Chinese_PRC_CI_AS
fetch next from IClost_cursor into @card_id
end
close IClost_cursor
deallocate IClost_cursor

GO

[zlp321002]

啥问题?这么一大篇文章...