Insert New Rows or Update existing rows

[ririr]

I am using OCI to perform Insert and Update operations. In my target OCI stage I have selected "Insert New Rows or Update existing rows". It creates the DML's.

I want to handle a CREATE_DATE column in the TARGET differently. I do not want to update the CREATE_DATE COLUMN value when the record is being updated, but I need to insert the CREATE_DATE COLUMN VALUE when it is INSERTING.

Can I do the above in the same stage?

Please advise.

Thanks

[auvray.muriel]

Why you do not put a default value (sysdate) in the script of creation of the table on fields "CREATE_DATE COLUMN" ?

With each new insertion the fields will be indicated by the date system. You do not need to treat it in your jobs, and it as that it is not updated in the event of update.

This method functions very well

[ririr]

Why you do not put a default value (sysdate) in the script of creation of the table on fields "CREATE_DATE COLUMN" ?

With each new insertion the fields will be indicated by the date system. You do not need to treat it in your jobs, and it as that it is not updated in the event of update.

This method functions very well

I don't understand what you are trying to explain..Please be more specific

Yes, I am writing the system date if it is an insert, but i don't want to update the create date column if it is an update.

[ray.wurlod]

What you really want is a slightly different job design.

"Insert New Rows or Update Existing Rows" is not efficient for updates; an insert has to be attempted and fail because the row already exists before the update is attempted.

A better design is to have two links into the database, one doing "insert new rows only", the other doing "update existing rows only". You determine which is which by performing a reference lookup either directly against the target table or (probably better) against a hashed file that has been loaded with the relevant key values from the target table.

Then you can use SYSDATE on the inserts and not update the date on the updates.

[ririr]

What you really want is a slightly different job design.

"Insert New Rows or Update Existing Rows" is not efficient for updates; an insert has to be attempted and fail because the row already exists before the update is attempted.

A better design is to have two links into the database, one doing "insert new rows only", the other doing "update existing rows only". You determine which is which by performing a reference lookup either directly against the target table or (probably better) against a hashed file that has been loaded with the relevant key values from the target table.

Then you can use SYSDATE on the inserts and not update the date on the updates.

Thanks for the update.

I appreciate!