topic Re: Merge Oracle : row updated or row inserted ? in Talend Studio

Merge Oracle : row updated or row inserted ?

Anonymous — Sat, 16 Nov 2024 12:34:38 GMT

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

Re: Merge Oracle : row updated or row inserted ?

Anonymous — Wed, 09 Nov 2011 17:12:13 GMT

Can you put a trigger on the table?

Re: Merge Oracle : row updated or row inserted ?

Anonymous — Thu, 10 Nov 2011 07:55:24 GMT

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

Re: Merge Oracle : row updated or row inserted ?

Anonymous — Thu, 10 Nov 2011 12:27:30 GMT

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.

Re: Merge Oracle : row updated or row inserted ?

Anonymous — Thu, 10 Nov 2011 14:31:55 GMT

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

Re: Merge Oracle : row updated or row inserted ?

Anonymous — Thu, 10 Nov 2011 14:41:55 GMT

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

Re: Merge Oracle : row updated or row inserted ?

Anonymous — Sat, 12 Nov 2011 11:59:11 GMT

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)