It is pretty common to desire both initial insert and update time columns added to a target table.
Of those two, the insert time is tricky as, as you observed, if you add that as a timestamp in Replicate it will also be updated during a subsequent update or delete.
The reason is that there is no option to NOT update a column once it is in the output list. There is a complex and costly workaround by using TARGET_LOOKUP and OPERATION_INDICATOR to set the value to itself on update and deletes. Obviously this will slow down the target actions.
The simple solution for the insert time is to NOT have it be added by Replicate but to use a target database provision to apply a default value. The only tricky part there is that you must switch the task to truncate for full-load and not drop+create as that would create without the insert time. Mind you, for most database you could also just add the insert time column with default value soon after the full-load and have a good enough result.
I urge you to go practice some with a test table in a test task. Add a bunch of columns loaded with all the available header variables and see for yourself when they are filled in or not (full-load vs insert vs update). Also add some columns with values from expressions based on the 'operation_indicator' function as per user guide and help. Trust but verify!
hth,
Hein.
