I have a requirement where I want to update the existing records to 'close' which has a status 'open' and I also want to add a new record for the same key with status 'open' and current timestamp. I am using oracle stage.
So if there are 5 open records then I should have 10 records in the output with one as closed and other as open for each one.
if I use update then insert in upsert mode its either updating and if it fails then it inserts. I am trying to design two jobs one for insert and another for update. Dont know if there is any other option ....
Could you send the rows down two links, one that does an update and one that does an insert. Link order should be update first. I assume that Timestamp is part of the key, so the new rows will not confilct with the updated rows.
Thanks for the response Mike actually time stamp field is not a part of key here.
what I am doing right now is extracting the update and insert into two different file and then the updating and inserting the table in two seperate job, which seems to be working good.