/*
--获取刷卡机中刷卡数据,需要先建服务链接,如下:
exec sp_addlinkedserver 'Ehrtest','','SQLOLEDB','192.168.8.210'
exec sp_addlinkedsrvlogin 'Ehrtest','false',null,'sa','digitalhr'
go
以代理运行维护计划时,计划中调用SQL格式库名.dbo.过程名称
传参日期格式:
二个都为'' 空,则以同步近天数据
二个都为'YYYY-MM-DD' ,则同步二日期区间点至点的数据
二个都为'YYYY-MM-DD HH:MM' ,则同步二日期区间二时间点间的数据
传参EmpNo格式:'00027' 以此建立本次同步的员式工号
*/
ALTER PROCEDURE [dbo].[usp_GetCardData]
@SDate nvarchar(20), --开始日期
@EDate nvarchar(20), --结束日期
@EmpNo nvarchar(500)='' --同步卡号指点
AS
Begin
Declare
@PersonID nvarchar(80), --员工ID
@CardNum nvarchar(8), --员工卡号
@DeviceNum nvarchar(2), --刷卡机号
@TIMECARDDATE nvarchar(10), --刷卡日期
@TIMECARDTIME nvarchar(8), --刷卡时间
@DEPARTMENTID nvarchar(80), --员工所在部门ID
@MachineID nvarchar(80) --刷卡机ID
IF(@SDate='' OR @EDate='')
BEGIN
SET @SDate=Convert(varchar(10),DateAdd(dd,-15,getdate()),120)+' 00:00'
SET @EDate=Convert(varchar(10),DateAdd(dd,1,getdate()),120)+' 00:00'
END
ELSE IF((charindex(':',@SDate)=0) OR (charindex(':',@EDate)=0))
BEGIN
SET @SDate=Convert(varchar(10),@SDate,120)+' 00:00'
SET @EDate=Convert(varchar(10),DateAdd(dd,1,cast(@EDate as datetime)),120)+' 00:00'
END
Create Table #Temp(cardnum nvarchar(40),devicenum nvarchar(40),timecarddate nvarchar(20),timecardtime nvarchar(20))
insert into #Temp
Select cardnum,devicenum,convert(varchar(10),cast(left(checkdate,10) as datetime),120),checktime
from vw_checkinout
where cast(checkdate+' '+checktime as datetime) between cast(@SDate as datetime) and cast(@EDate as datetime)
order by cardnum,checktime asc
declare @countA int
select @countA=count(*) from #Temp
print '数量为:'+convert(nvarchar(20),@countA)
DECLARE curGetData CURSOR FOR
select cardnum,devicenum,timecarddate,timecardtime from #Temp order by cardnum asc
-- Select cardnum,devicenum,convert(varchar(10),cast(left(checkdate,10) as datetime),120),checktime
-- from vw_checkinout
-- where cast(checkdate+' '+checktime as datetime) between cast(@SDate as datetime) and cast(@EDate as datetime)
-- order by cardnum,checktime asc ---this line add 20141127
OPEN curGetData FETCH next from curGetDat
SqlServer数据通过DBLink 同步到Oracle小例 (不积跬步,无以至千里)
最新推荐文章于 2023-08-16 14:22:04 发布
这个SQL存储过程展示了如何通过DBLink从SqlServer同步数据到Oracle。它首先创建服务链接,然后根据指定的日期范围和员工卡号,将刷卡数据插入到Oracle的ATDTIMECARDDATA表中。如果数据已存在,则不会重复插入。
摘要由CSDN通过智能技术生成