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
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
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.
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
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)