How to use Lookup Transformation for Incremental data loading in target table?

Scenario:

how to load new data from one table to another.

     For eg: i have done a mapping from source table (Which contain bank details) to target table.

               For first time i will load all the data from source to target, if i have run the mapping second day,

               i need to get the data which is newly entered in the source table.

               First time it have to load all the data from source to target, for second or third time, if there is any new record in the source table, only that record must load to the target, by comparing both source and the target.

               How to use the lookup transformation for this issue?

Solution:

1)      In mapping, create a lookup on target table and select dynamic lookup cache in property tab, once you check it you can see NewLookupRow column in lookup port through which you can identify whether incoming rows are new or existing. So after lookup you can use router to insert or update it in target table.

Also in lookup port, you can use associate port to compare the specific/all columns of target table lookup with source column.its a connected lookup where you send a source rows to lookup as input and/or output ports and lookup ports as output and lookup.

OR

2) If there is any primary key column in the target table then we can create a lookup on the target table and match the TGT primary key with the source primary key.If the lookup finds a match then ignore those records ,if there is no match then insert those record into the target

The logic should be as below

SQ--> LKP--> FILTER-->TGT

In lookup match the ID column from src with ID column in the target .The lookup will return the ID's if  it is avail in the target else it will return null value.

In filter allow only null ID values which is returing from the lookup.

OR

3. If you have any datestamp in the source table then you can pull only the newly inserted records from the source table based on the time stamp (this approach will applicable only if the source table has a lastmodifieddate column).