Hi Rikab,
3 Types of Incremental Loads:
1. Only Insertion in the table
2. Insertion & updation in the table
3. Insertion, Updation & Deletion in the table
P1 - Primary Key 1
C1 - Column 1
C2 - Column 2
D1 - Date Field 1
Only Insertion in the table:
Let say there is only insertion of record is happing in a table (T1). Now you want to do the incremental load for the table. You need to have a date field (D1) in the table (T1);
First step before doing a incremental load is to get the current snap shot of the table and convert into QVD. Pls find the eg code for the above situation;
T1:
SQL Select P1, C1, C2, D1 from T1 where D1= "Yesterday's Date"; à This set has only yesterday's data retrieved from db.
Load P1, C1, C2, D1 from T1.QVD where D1<"Yesterday's Date"; à This set has data less than the yesterday's date.
Store T1 as T1.QVD à Now the T1 QVD has full set of data until yesterday.
Insertion & Updation:
Let say there is insertion & updation of record is happing in a table (T1). Now you want to do the incremental load for the table. You need to have a primary (P1) and date field (D1) in the table (T1);
First step before doing a incremental load is to get the current snap shot of the table and convert into QVD. Pls find the eg code for the above situation;
T1:
SQL Select P1, C1, C2, D1 from T1 where D1= "Yesterday's Date"; à This set has yesterday's both inserted & updated data retrieved from db. Here the date field should affect by both insertion & updation of the record in the db.
Load P1, C1, C2, D1 from T1.QVD where D1<"Yesterday's Date" and not exists(P1,P1); à This set has data less than the yesterday's date but not the updated records (updated records are not available in this set because of NOT EXISTS command).
Store T1 as T1.QVD à Now the T1 QVD has full set of inserted & updated data until yesterday.
Insertion, Updation & Deletion:
Let say there is insertion, updation & physical deletion of record is happing in a table (T1). Now you want to do the incremental load for the table. You need to have a primary (P1) and date field (D1) in the table (T1);
First step before doing a incremental load is to get the current snap shot of the table and convert into QVD. Pls find the eg code for the above situation;
T1:
SQL Select P1, C1, C2, D1 from T1 where D1= "Yesterday's Date"; à This set has yesterday's both inserted & updated data retrieved from db. Here the date field should affect by both insertion & updation of the record in the db.
Load P1, C1, C2, D1 from T1.QVD where D1<"Yesterday's Date" and not exists(P1,P1); à This set has data less than the yesterday's date but not the updated records (updated records are not available in this set because of NOT EXISTS command).
Inner Join SQL Selection P1 from T1; à This command executes only current available P1 records. Where it removes the records from above loaded Table T1 if it is not available in db.
Store T1 as T1.QVD à Now the T1 QVD has full set of inserted & updated data until yesterday.
I hope the above to understand better.
