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
Difference between Insert else Update and Update else insert
Moderators: chulett, rschirm, roy
"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.
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
![Wink :wink:](./images/smilies/icon_wink.gif)
In theory there's no difference between theory and practice. In practice there is.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
And as ogmios notes, it is vital that the indexes on your target table support these mixed actions.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers