update and insert

samsuf2002 · Post by **samsuf2002** » Tue May 12, 2009 8:48 am

Hi,

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

Can anyone suggest me on this .

Thanks in advance

mikegohl · Post by **mikegohl** » Tue May 12, 2009 9:34 am

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.

samsuf2002 · Post by **samsuf2002** » Tue May 12, 2009 9:44 am

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.

mikegohl · Post by **mikegohl** » Tue May 12, 2009 9:51 am

Somehow, the key has to allow for two records to be in the database at the same time. What makes the rows unique in the database?

ray.wurlod · Post by **ray.wurlod** » Tue May 12, 2009 3:39 pm

Slowly Changing Dimension stage can do this for you.