Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Current script below. This works great for finding newly created incidents from the last load but now I want to update my QVD incidents where sys_updated_on has been updated.
I think I need to load the old data, query for new incidents where sys_updated_on > max(sys_updated_on), and then load old where not in new? This is what I don't know how to accomplish with ODBC SELECT vs LOAD.
tbl_incidents:
LOAD inc_number,
enduser,
agent,
opened_at,
SDCI,
short_description,
sys_created_on,
sys_updated_on
FROM [incidents.qvd] (qvd);
MaxDate:
LOAD max(sys_created_on) as MaxDate
Resident tbl_incidents;
LET vMaxDate = chr(39) & TimeStamp(PEEK('MaxDate', 0, 'MaxDate')) & chr(39);
DROP TABLE MaxDate;
Concatenate select t.number_ as inc_number,
t.u_requested_for as enduser,
t.opened_by as agent,
t.opened_at,
sdci.name as SDCI,
t.short_description,
t.sys_created_on,
t.sys_updated_on
from t_incident t
left join t_sdci sdci on sdci.id= t.sdci
where t.sys_created_on >= $(vMaxDate);
store tbl_incidents into 'incidents.qvd'(qvd);
You can check the QV cookbook by Rob, which shows a decent incremental load example ("delta load template") application including insert, delete and update of records: