Oracle Inserts

dodda · Post by **dodda** » Wed Mar 25, 2009 12:11 pm

Thanks for your help

Just wondering whether UPSERT mode works for only inserts?

Thanks

chulett · Post by **chulett** » Wed Mar 25, 2009 12:15 pm

Sure, but the question is in what order are the operations performed? Either way will work as desired - provided the updates are filtered out - but best to perform the insert attempt first so that you don't burn two database operations for every record. I don't have any kind of access right now, do you have control over if it does "insert else update" versus "update else insert"?

dodda · Post by **dodda** » Wed Mar 25, 2009 12:27 pm

Thanks

Well these are the options

If we mention Write method as UPSERT then i can see options under Upsert Order (Insert then Update) and (Update then Insert). According to what I understood is as i am filtering out Updates in can use Insert then update as Upsert order. Am i Right?

Thanks

chulett · Post by **chulett** » Wed Mar 25, 2009 12:29 pm

As noted, either will work but "Insert then Update" would be the smarter choice.

dodda · Post by **dodda** » Wed Mar 25, 2009 12:32 pm

Thanks

But under Upsert mode i can see 4 options

Autogenerated Update & Insert
Autogenerated Update only
user defined Update& Insert
user defined Update only

can i go for First option?

Thanks

betterthanever · Post by **betterthanever** » Wed Mar 25, 2009 1:22 pm

yes you can go for that...unless you want to mention INSERT/UPDATE statements manually.

chulett · Post by **chulett** » Wed Mar 25, 2009 3:04 pm

Since for whatever reason you don't seem to have "Insert then Update" as an option, then yes - go manual and include only the insert dml.