Incremental Load using Record IDs in SQL script

[#1]

I have large database table (~40 Million Records) which adds almost 500,000 records a day.

I am creating Incremental load where I have loaded the 40 Millions records to one QVD file and planning to append to the QVD files the new records added everyday based on the new IDs only.

Here is how I am trying to do it:

First step: load the saved QVD file of the historical data

CALLDROPS:

LOAD

num#(ID) as ID

,left(ID,6) as ID_DATE //this part will give me unformatted YYYYMM date ie: '201007'

,item1

,item2

FROM [..\QVD Data files\CALLDROPS.QVD] (qvd);

Second step: find the max ID_DATE (ID_DATE=YYYYMM)

CALLDROP_MAX_ID_DATE:

LOAD max(ID_DATE) as MaxID_DATE

Resident CALLDROPS

Third step: save the MaxID_DATE to a variable

Let vMaxIDDate= MaxID_DATE

Forth step: load connate to the historical QVD table

concatenate (CALLDROPS)

LOAD *

where not exists(ID);

SQL SELECT ID,

items 1,

items 2,

.....

FROM database table

where SUBSTR(ID,1,8) >=$(MaxID_DATE);

Store CALLDROPS into "..\QVD Data files\CALLDROPS.QVD";

Drop TABLE CALLDROPS;

The part which is not working for me is the third step where when I display the variable value (which I use in the where statement ) is displays NULL.

[hector]

Hi, try changing this line

Let vMaxIDDate= MaxID_DATE

for this

Let vMaxIDDate= peek('MaxID_DATE',0,'CALLDROP_MAX_ID_DATE');

Rgds

[#3]

thanks alot. it works and I have passed this step. I will go ahead and test the next step and let you know 🙂