One Star doc
One Star

update, insert AND delete old rows

Hello,
I'm working on Talend 4.01 and I have a question about how to update, insert AND delete old rows.
I have a job that runs every night. In this job, the input file is updated each day (I mean that in this file, there are new rows, rows that are updated and rows that don't exist anymore).
At the end of my job, I want my Oracle table to be updated with this file (I mean : add the new rows, update the rows that have changed AND delete the rows that don't exist anymore).
I use a tOracleOutput but I don't know if there's a way to update/insert AND delete old rows.
In fact "insert or update" and "update or insert" don't include "delete old rows".
What do you think I should do ?
thanks a lot
manuelle
5 REPLIES
One Star mpa
One Star

Re: update, insert AND delete old rows

Hi,
In this case I would just truncate the table each night Smiley Tongue.
I guess you could use an tMap and do a lookup to decide it's a new/existing or a record that doesn't exist anymore.
Then your tMap will have 2 outputs. 1 output for the new/existing records that goes to a tOracleOutput using the "update or insert" as you suggested.
The other output then would be the deletion.
Regards
One Star

Re: update, insert AND delete old rows

This is how I do it in MySQL
With a better image.
One Star doc
One Star

Re: update, insert AND delete old rows

Thanks for your replies,
MPA, considering the truncate solution, would you simply do a "truncate" and then "insert" in the table ? would it be right ?
Considering the tMap solution, you would do a look up with the existing oracle table, wouldn't you ? And then create the two outputs (new/existing and delete) going to two tOracleOutputs of the existing table, wouldn't you ?

Sorry Janhess, even if I understand the global image, MPA's two answers seem simplier Smiley Surprised)
Thanks both of you
manuelle
One Star

Re: update, insert AND delete old rows

The first image represents the delete stage comparing existing database table to input file and deleting missing records from the input file, then does the insert update. Second image is just the complete job with logging of the activity. So this is the implementation of MPA's second solution.
One Star

Re: update, insert AND delete old rows

how i create scd for mongodb