Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

Merge Oracle : row updated or row inserted ?

Highlighted
One Star

Merge Oracle : row updated or row inserted ?

Hi,
I'm using TOS 4.2 to feed an Oracle table from another Oracle Table.
My design is quite simple :
tOracleInput --> tMap --> tOracleOutput
What I want is to do a merge, based on the target table primary key : update when the row exists, insert if not.
So, in the "action" field of my tOutputOracle component, I use the "Insert or Update" parameter.
Here, everything works.
Now, what I want is to distinguish updated rows than inserted ones.
(in order to feed my CREATION_DATE and UPDATE_DATE fields in the target table)
Could someone explain me how I could do this ? I tried to use variables in the tMap editor but without success...
Thanks for your help !
David
Highlighted
One Star

Re: Merge Oracle : row updated or row inserted ?

Can you put a trigger on the table?
Highlighted
One Star

Re: Merge Oracle : row updated or row inserted ?

Thank you walkerca,
An Oracle trigger is a solution... and a good one I think.
But currently I'm discovering Talend, and as a training, I'd prefer to do the most thing using Talend only (when it's possible of course).
In fact, I guess I could do something with a lookup on my target table... I'm going to try this today (and let you know if I found something interesting).
David
Highlighted
One Star

Re: Merge Oracle : row updated or row inserted ?

The trigger is best if there could be a non-Talend application working with the table so that the fields are always handled correctly.
If you want a Talend-only solution, take 2 passes on the input data. The first pass does a strict update on existing records, mapping TalendDate.getCurrentDate() to the UPDATE_DATE field.
Then, map TalendDate.getCurrentDate() to the CREATE_DATE using an insert action on the oracle output component.
Highlighted
One Star

Re: Merge Oracle : row updated or row inserted ?

Ok,
I tried this and it works perfectly !
Thanks !
David
Highlighted
One Star

Re: Merge Oracle : row updated or row inserted ?

An other way to do such thing could have been to seperate the insert and the update in 2 subjob.
First you use a tmap to make an inner join with your table in order to update rows that already exist.
tOracleInput
|
tOracleInput------tmap----------tOracleInput---> no insert just update
Second you also use a tmap, make an innerjoin but catch the reject in the output so it will only treat the rows that didn't already exist
tOracleInput
|
tOracleInput------tmap----------tOracleInput(with catching reject) ----> only insert
Highlighted
One Star

Re: Merge Oracle : row updated or row inserted ?

Hi
I am TOS 4.2 .
We are insert , update, delete data from one database table to another database table. But we are facing a problem whenever i m selecting "Use
update (when matched). And unable to insert the data in target table.
Design:-
tmsqlconnection_1...........................tmssqlinput...............................tlogrow_1
tmsqltemplatemerg_1
.tmssqlinput...............................tlogrow_1

Error message:-
Exception in component tSQLTemplateMerge_1
java.sql.BatchUpdateException: Incorrect syntax near ','.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947)
at csd.csd_0_1.CSD.tSQLTemplateMerge_1Process(CSD.java:1178)
at csd.csd_0_1.CSD.tMSSqlInput_1Process(CSD.java:997)
at csd.csd_0_1.CSD.tMSSqlConnection_1Process(CSD.java:383)
at csd.csd_0_1.CSD.runJobInTOS(CSD.java:2103)
at csd.csd_0_1.CSD.main(CSD.java:1971)

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog