CODEWITHSUNDEEP
oracle-databaseetltalend
Paul
Paul

Reputation: 1483

How to do a simple Talend Upsert with timestamp column

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.

tOracleOutput upsert

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

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.

Upvotes: 2

Views: 2465

Answers (1)

E LaRoche
E LaRoche

Reputation: 1144

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.

Upvotes: 3

Related Questions