Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community, I have the following challenge: I need to upload my data incrementally and add new ones and update existing records.
Current Script
vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD');
DO WHILE vDate <= Today(1)
/* TRACE */ SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD');
LET vDate = Date(Date#('$(vDate)', 'YYYYMMDD')+1 ,'YYYYMMDD');
LOOP
STORE VENTA_GC_2018 into [lib://QVDS/]VENTA_GC_2018.qvd;
//COMBINE NEW DATA WITH EXISTING QVD
CONCATENATE (VENTA_GC_2018)
LOAD *
FROM [lib://QVDS/VENTA_GC_2018.QVD] (QVD)
WHERE NOT EXISTS(NIS_RAD)
Here is how I do my incremental load since the full table is about 14 million rows and takes forever. The one drawback is that I DO NOT add in updated records. This script could be more genericized to make it more robust.
LET vQVDexists = NOT ISNULL(QVDCreateTime('lib://blahblah/v3\ExpediteAuditExtract.qvd'));
LET vNow = Now();
IF $(vQVDexists) THEN;
TRACE('QVD Exists, so we are going to append');
LatestUpdate:
LOAD Date(Max(updated_dt)) as MaxDate
FROM [lib://blahblah/v3\ExpediteAuditExtract.qvd]
(qvd)
;
Trace('Got to the LatestUpdate');
LET vMaxDate = Peek('MaxDate',0,'LatestUpdate');
TRACE('Max update' & '$(vMaxDate)');
NewData:
LOAD *
;
SQL SELECT stuff
FROM "TableOfInterest"
WHERE updated_dt > '$(vMaxDate)'
;
Trace('Concatenating the new data with the old data');
Concatenate(NewData)
LOAD * FROM [lib://blahblah/v3\ExpediteAuditExtract.qvd]
(qvd)
;
STORE NewData into [lib://blahblah/v3\ExpediteAuditExtract.qvd] (qvd);
DROP Tables NewData, LatestUpdate
;
ELSE
Trace('QVD does not exist, so we are doing a full load');
DATA:
LOAD everything...
;
SQL SELECT everything FROM...
;
STORE DATA into [lib://blahblah/v3\ExpediteAuditExtract.qvd] (qvd);
DROP Table DATA
;
END IF
;
This doesn't remove stale records and bring in updated records. But I believe this could be done using the whole "WHERE NOT EXISTS ('somekey','somekey')" construct where you don't concatenate the old records if their primary key already exists.
Hope this helps.
It still does not work, it does not add all the records, just a few.