update, insert AND delete old rows

[Anonymous]

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

[Anonymous]

Hi,
In this case I would just truncate the table each night .
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

[janhess]

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

[Anonymous]

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 )
Thanks both of you
manuelle

[janhess]

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.

[Anonymous]

how i create scd for mongodb