关于数据增量抽取的模拟实现——脚本实现

CREATE PROCEDURE p_org_Extract
\3Id6W)M0ASITPUB个人空间}-` eAQp$_9P
DECLARE @sql VARCHAR(3000)

BEGIN

DECLARE @BeginDateDATETIME,ITPUB个人空间w&A!q-O iTi
@EndDateDATETIME,
8gsy M wy5C+gd'g uE0@TaskNameVARCHAR(32),
"k8hc!R^G pe0@FlagINTEGER,
3\ C{ |.K&|O0@NumINTEGER,
S1_%v8NNlRSB u0@CurrDateDATETIME

SELECT @Num = COUNT(TaskName) FROM ExtractTaskListITPUB个人空间4}%mLI*SQs-W#T`V
WHERE UPPER(TaskName) = UPPER('test')

IF @Num != 1ITPUB个人空间]h9X
H1m6@
INSERT INTO ExtractTaskList(TaskName,IncType,TransType) VALUES('test',2,2)

--获取列表中的当前任务的时间戳和状态
lF0a;nOH4X+Y6M J0SELECT @BeginDate = SourceDate,@Flag = Flag FROM ExtractTaskList WHERE TaskName='TEST'

--如果上次执行未成功，这样取值效率会高一些，则从数据仓库表中直接读取ITPUB个人空间!bK
\!cDY#D8gy
--TargetDate和SourceDate可能会不一致
_-kI \2T6X%tCHk0IF @Flag = 2 OR @Flag IS NULL
;VXFfj@0SELECT @BeginDate = DATEADD(ss,1,MAX(closedate)) FROM TargetTable

--如果数据仓库无数据，则从业务系统中直接读取，也可以设置一个默认的初始化时间ITPUB个人空间/pOoB"Be@v vz4X
IF @BeginDate IS NULLITPUB个人空间A.ll4B,SFo
SELECT @BeginDate = MinLogDate FROM OPENQUERY(SOURCE,'SELECT MIN(CloseDate) AS MinLogDate FROM SourceTable')

--如果仍无数据，则表示无数据可抽取，退出执行
)rmd&KW2It0IF @BeginDate IS NULLITPUB个人空间,a
Q!R,I#q1c L
RETURNITPUB个人空间ck},_o$p{p

5a+n0aH%r8i9b0--抽取结束时间为当前时间前一天,每次循环抽取1天数据,可以更改dd为hh，变成按小时抽取
/ag'f8QB$O^g _0--通常业务系统是连续的，如果有疑问也可以从业务系统中获取最大时间ITPUB个人空间v4|N'k1|8q
SELECT @EndDate = CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,GETDATE(),120),10)+' 00:00:00')

--更新当前开始时间和结束时间
CY;{x!C8a$bn0UPDATE ExtractTaskList
#K(}&Hh e)Hm0SET TargetDate = @BeginDate,
kvf-pA0SourceDate = @EndDate
1M;wr._JP| {?0WHERE UPPER(TaskName) = UPPER('test')
f(O;A Zk1gDs0
PY,C*K.K8e0WHILE @BeginDate < @EndDateITPUB个人空间"At*zX;wF.J_2D
BEGINITPUB个人空间iBt)LC/`s nI!Y5d
SELECT @sql = ' INSERT INTO TargetTable
\ G&t5_sW X0(ITPUB个人空间y!T-m2|"J]Ip
ID1,
\2?4z}0y U}4i,J)T0ID2,
e:ac3p`Z$PP#ka:L0Measure1,
s,I
f+~-iLN(M0Measure2,
j{o{xn3w~0CloseDateITPUB个人空间 W#|M9G~$ccG VFBC
)SELECT * FROM OPENQUERY(SOURCE,''selectITPUB个人空间L7sH2v"?2Ec
ID1,ITPUB个人空间k
G$R:Qa7t(]n
ID2,ITPUB个人空间 z!L;L%XFk
Measure1,
S6zRv3`'^LwU"i(|0Measure2,ITPUB个人空间a)R3P*dP|0Y,]
CloseDate
i r)wNY0FROM SourceTableITPUB个人空间T X
px(f9R*v!p
WHERE CloseDate >= TO_DATE(''''' + CONVERT(varchar,@BeginDate,120) + ''''', ''''YYYY-MM-DD HH24:MI:SS'ITPUB个人空间7Ln@ry
+ ''''') AND CloseDate < TO_DATE(''''' + CONVERT(varchar,DATEADD(day,1,@BeginDate),120) + ''''', ''''yyyy-mm-dd HH24:MI:SS'ITPUB个人空间'^.J#Ch;VY"A7h)C7o
+ ''''') AND CloseDate < TO_DATE(''''' + CONVERT(varchar,@EndDate,120) + ''''', ''''YYYY-MM-DD HH24:MI:SS'
$r,n2~{g+OO0+ ''''')'')'
h\ J4~'?9sCG9d1U-I0--PRINT @sql
*_ @/LQ;|k7H3r/E0EXEC (@sql)
xk{b0?B0
,i aol:zD9EC0--获取本次任务运行抽取的最大时间ITPUB个人空间*@*M8v/o9_A
IF DATEADD(day,1,@BeginDate)>@EndDateITPUB个人空间P]4r8n-w
SELECT @CurrDate = @EndDateITPUB个人空间%ik:a^:p#n L7q
ELSE
|T`7Z*_,\%SX0SELECT @CurrDate = DATEADD(day,1,@BeginDate)

--如果@sql执行失败，同样记录状态和时间ITPUB个人空间p;yzno*z`yB
IF @@ERROR <> 0ITPUB个人空间.^ DF+D
e#d2t]:|j%x
GOTO FAIL

--记录每次运行的时间运行情况，可提供相应参考ITPUB个人空间6Oq\9d
T!Yk
UPDATE ExtractTaskListITPUB个人空间{2l vQeu;V
SET TargetDate = @CurrDate,
&|0cLq ^4Ah#n0Flag = 1ITPUB个人空间b(R,Q`cv
WHERE UPPER(TaskName) = UPPER('test')

SELECT @BeginDate = DATEADD(DD,1,@BeginDate)

END

RETURN

FAIL:

--记录错误

UPDATE ExtractTaskList
$N+\!D T~.a)]0SET TargetDate = @CurrDate,
+yp,t4[f0Flag = 2ITPUB个人空间5Y+o5{A.qoQ#Y'_o S
WHERE UPPER(TaskName) = UPPER('test')

RETURN 0

END