Requirement to do bulk upsert in oracle using JDBC adapter

Hello Experts,

One of the requirement we have encountered is to do batch upsert in oracle DB table.
While doing so we have come up with, first to sort the data into 2 list depending on whether the data is available in the target table or not. Then to batch insert the list of data which is not in the target table and to batch update the list of existing records.

But as you can see this is quite cumbersome. Do you think there is any other easy and straight forward way to do the same?

Debapriyo Dasgupta


If all of your data is already in the same database, Iā€™d urge you to do all the operations inside it, maybe creating a stored procedure to start it from the Integration Server.

If you have to retrieve data from another source to be upserted in the target database, the best option would be to use a temporary table, which you empty at the start of your procedure, and batch insert them there, so you can proceed with a pure DB upsert operation.

The idea is to avoid doing any data intensive operations outside the database.

Best regards,