Page 1 of 1
Upsert problem
Posted: Mon Oct 25, 2004 5:55 am
by weiven
Im trying to INSERT value from Sequential file to Oracle9i using method UPSERT.
For UPSERT method, it should only insert data that does not exist in the database.
My problem is, why it INSERT duplicate value into the database when i rerun it again?
Plz help!?
Posted: Mon Oct 25, 2004 7:17 am
by tonystark622
Check and make sure that you have the right columns defined as keys.
Good Luck!
Tony
Re: Upsert problem
Posted: Mon Oct 25, 2004 9:22 am
by prabu
weiven wrote:Im trying to INSERT value from Sequential file to Oracle9i using method UPSERT.
For UPSERT method, it should only insert data that does not exist in the database.
My problem is, why it INSERT duplicate value into the database when i rerun it again?
duplicate based on what???. try trimming the values the key columns before inserting .
Posted: Mon Oct 25, 2004 1:06 pm
by gh_amitava
Hi,
It seems that in "Upsert", DataStage does update first and if not found then insert but I also faced the similar problem. In fact Datastage does an "Insert" first. So duplicate records may exists in this case. So to avoid this situation you have to handle it by Database unique key constraints.
Regards
Amitava
Posted: Tue Oct 26, 2004 9:24 am
by dsxdev
You can do a look up just before inserting and use only those records which fail to match in the lookup Stage this would solve the issue of duplicates.