What's the best approach to load the target table from the source incrementally.Let's say we have 1 million rows.I need to load the table for every 1 lakh.
Tools are always better. But if you dont have tools, then you have to use queries. You can think of this option,keep one table to track load based on identifiable field or fields.Use row_number() and qualify to restrict rows you want and insert the required data.
There may be other ways too.
You have not told the source is (table or file) or some othe DBs etc.
Why do you want to load in bunches of 100.000 rows?
This will be (potentially much) slower than loading all rows in a single step.