I have been thinking of creating a brand new Knowledge Module which will make an Incremental Update without using any UPDATE/DELETE SQL statements, only with inserts to increase performance. Initially, I was thinking that there should be a second integration table, but when I started scrawling to figure out flow diagram and how Knowledge Module works, I realized that there is no need for a second integration table.
If you are using Oracle database which your target table existing, this Knowledge Module can fit your needs. Let me try to explain how it works;
1. Drops and Creates I$ flow table (as usual)
2. Inserts the missing records (new records on source) to I$ flow table (if INSERT_NEW_ROWS option is set to true).
3. Inserts the matching records (existing records on source) to I$ flow table (if UPDATE_EXISTING_ROWS option is set to true).
4. Inserts the non-matching records (deleted records on source) to I$ flow table (if NOT_DELETE_NON_EXISTING_ROWS option is set to true).
5. Wrap them up and truncate & insert target table from flow table and drop flow table (if DELETE_TEMPORARY_OBJECTS option is set to true).
6. Analyze target (if ANALYZE_TARGET option is set to true). Continue reading