Difference between Insert else Update and Update else insert

Anand K · Post by **Anand K** » Wed Dec 22, 2004 2:31 am

Hi All

Can any one suggest on which all situations we need to use the "Insert new rows or Update existing rows" and "Update existing rows and Insert new rows" in ORAOCI8 Stage?

Thanks in Advance
Anand

Sridhar G · Post by **Sridhar G** » Wed Dec 22, 2004 2:51 am

Hi Anand,
From the experience that we have had, i feel it is better to use Update else Insert b'coz, if you update first, whenever a matching record is found, it immediately updates and goes to the next record. Else, it waits till all the insert is completed and then only updates.

Anand K · Post by **Anand K** » Wed Dec 22, 2004 3:00 am

Thanks for the quick response, Sridhar.

So what I understand is for records running into millions, we need to use the Update else Insert and for smaller amount of data we need to use Insert else Update.

Anand

ogmios · Post by **ogmios** » Wed Dec 22, 2004 5:29 am

"Insert else update" will first try to insert the row and if that fails try to update. "Update else insert" will first try the update and if that fails (no corresponding row exists) the insert.

If you have the keys defined in your database and DataStage in a proper way both are the same qua functionality. But e.g. if would use them on a table without keys on the database (but with 'correct' keys defined in DataStage) you will see differences: "Insert else update" would then always succeed on the insert (since it would never get a duplicate error from the database) and insert duplicate rows, while the "Update else insert" would still work properly (in the case that the keys are defined properly in DataStage).

In the assumption that the keys are correctly defined in database and DataStage the rule would be:
- If you know most of your input rows are updates use "Update else Insert"
- If you know most of your input rows are new inserts use "Insert else Update"

Ogmios

P.S. Don't know what the comment was on waiting till insert is complete

. I do know that in Oracle actually updating something is much slower as inserting.

chulett · Post by **chulett** » Wed Dec 22, 2004 8:22 am

All in all, you are much better off not using either of them. IMHO.

Best to make explicit reference checks so you know which is which and split your output stream, doing seperate inserts and updates.

And as ogmios notes, it is vital that the indexes on your target table support these mixed actions.