I want to do delta load for a table, where there is no date field instead there is a flag field.
This table is not insert only, existing records will also get updated (if there is a change in the record, then we will expire the old record with currentflag status ‘N’ and insert new record with currentflag ‘Y’).
Can someone help me with the script to achieve delta load for this table.
I'm sorry i do not have any sample data for this requirement, intead i collected few information as below;
1. This table holds Employee details.
2. A dimension table which holds millions of records
3. Insert and Update table eg., New employee added will be insert, and salary, promotion details will be updated. No delete of records from employee table.
4. Doesnt have any of the date field to proceed with the delta logic.
5. Instead there is a "CurrentFlag", on top of this field delta logic is to be applied.
6. Current Flag - Holds only two data in this column which Y and N.
5. if there is a change in the record, then will expire the old record with currentflag status ‘N’ and insert new record with currentflag ‘Y’.
Try use this:
WHERE flag= 'Y'; //Load only modified data from db
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT Exists(PrimaryKey); //Add all unmodified data from QDV
STORE QV_Table INTO File.QVD; //store back updated data into qvd