How to do a simple Talend Upsert with timestamp column

Question

I'm trying to create a Talend job which will do the following:

1. Take input rows and insert or update into an oracle table
2. If the row already exists in Oracle table, check if it needs updating based on primary key matching.
3. If the row does not exist in Oracle table, insert a new row.

These 3 are simple and have been achieved using the "Insert Or Update" action on data in a tOracleOutput component.

However, my problem is that I have 2 extra columns in the Oracle table:

• DM_UPDATE_DATE
• DM_INSERT_DATE

If an update occurs, I need to set the current datetime in the DM_UPDATE_DATE column, but only if the row is updated.
If an insert occurs, I need to set DM_INSERT_DATE and DM_UPDATE_DATE to the current datetime.
I have not been able to achieve this behaviour - is there an out of the box Talend solution for this?

I have looked at the tMap component, in particular this SO Answer appears to have outputs from the tMap component to differentiate between updates and inserts, but this seems overly complicated for something that is pretty standard in ETL jobs.

Answer 1

Paul,

Here is the solution:

Since mod date is set on both updates and inserts, set those in a tmap. See code, but keep in mind this returns a string, so use string as the data type in the tmap, and varchar as the datatype in your oracleoutput component.

TalendDate.getDate("CCYY-MM-DD hh:mm:ss")

For insert date use an Oracle default on the table, and do not send in a value via Talend.