Upsert problem

weiven · Post by **weiven** » Mon Oct 25, 2004 5:55 am

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!?

tonystark622 · Post by **tonystark622** » Mon Oct 25, 2004 7:17 am

Check and make sure that you have the right columns defined as keys.

Good Luck!
Tony

prabu · Post by **prabu** » Mon Oct 25, 2004 9:22 am

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 .

gh_amitava · Post by **gh_amitava** » Mon Oct 25, 2004 1:06 pm

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

dsxdev · Post by **dsxdev** » Tue Oct 26, 2004 9:24 am

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.

DSXchange

Upsert problem

Upsert problem

Re: Upsert problem