Integration Cloud Service (ICS) – How to use Upsert Operation by Shalindra Singh


Oracle launches ICS around April, 2015 and is being accepted by customer well. As customers started using ICS for  Salesforce Integration,  one question was observed and asked by lots of customers, is related to Upsert operation usage. We will dig deep into this in this blog.

Salesforce to ERP Integration common scenarios are –

  1. Account and Contact Sync
  2. Product and Price Sync
  3. Opportunity or Quote to Order (Sales Order)
  4. Order Status Update back to
  5. Invoice Data Transfer to

Other Integration flows include, History, Attachments,  Warranty, Cases, etc…

In most of the scenarios where data needs to be inserted or updated into, recommended operation is Upsert to be used. When Upsert Operation is leveraged, Salesforce SOAP API automatically check based on external Id defined whether incoming record exists or not; if it does exist then record gets updated or else inserted. Click here for more information on upsert operation.

But when you start using Upsert operation with any standard or custom objects from, by default, external id is not defined in So if you have decided to use upsert operation in an integration flow, first thing to do is to define / create external id in the object(s) you are planning to use with upsert operation.

Creation of External ID:

External Id is important parameter for upsert operation. To avoid data duplication external id should be created with utmost care. Best practice suggests to use foreign key (unique key) as an external key. if foreign key is composite key, external key should also be composite. It allows you to create or update a record and relate it to another existing record in a single step instead of querying the parent record ID first.

Let’s take an example here. consider I am working on Account Sync and I am bringing customers master data from ERP to So to create external id in Account object, login to and then navigate to setup -> App Setup -> Customize -> Account -> Fields. To create a new fields, click on new button on this page. Select the data type (for example Text) and click to Next. Provide name for this external id field (appending ext_id in the name is considered best practice) and select external id check box on this page as shown in the below diagram. Read the complete article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

About Jürgen Kress
As a middleware expert Jürgen works at Oracle EMEA Alliances and Channels, responsible for Oracle’s EMEA Fusion Middleware partner business. He is the founder of the Oracle SOA & BPM and the WebLogic Partner Communities and the global Oracle Partner Advisory Councils. With more than 5000 members from all over the world the Middleware Partner Community is the most successful and active community at Oracle. Jürgen manages the community with monthly newsletters, webcasts and conferences. He hosts his annual Fusion Middleware Partner Community Forums and the Fusion Middleware Summer Camps, where more than 200 partners get product updates, roadmap insights and hands-on trainings. Supplemented by many web 2.0 tools like twitter, discussion forums, online communities, blogs and wikis. For the SOA & Cloud Symposium by Thomas Erl, Jürgen is a member of the steering board. He is also a frequent speaker at conferences like the SOA & BPM Integration Days, JAX, UKOUG, OUGN, or OOP.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: