DATA MIGRATION & FLOW TUTORIALS

Search

When to Use Salesforce Data Loader’s Update vs. Upsert Action

Data Loading

Apr 27, 2022

Email
Facebook
LinkedIn
What’s the difference between data loader’s update and upsert actions? When should you use upsert?

Upsert is not a real word. 

(The English major in me would be remiss to not get this out of the way first.)

Dictionary search results for Salesforce data loading term UPSERT

Dictionary search results for Salesforce data loading term UPSERT

Although it’s not recognized in the dictionary, it is a well known portmanteau in the Salesforce lexicon. A portmanteau is created when parts of separate words are combined to make a new word, like smog.

Or maybe……..

Salesforce UPSERT was created by aliens

Salesforce UPSERT was created by aliens

So what exactly is an upsert?

Upsert Definition

Upsert is data loading’s method of running two processes simultaneously – an update and an insert. When a record in your file matches an existing record based on the record’s ID, the existing record is updated with the values in your file. If no match is found, a new record is created. 

Record ID

If you are performing an upsert, your CSV file must contain a column of ID values for matching against existing records. The column is either an external ID (a custom field with the External ID attribute) or ID (the Salesforce record ID).

What is an External ID?

You can set a Salesforce field as an external ID to reference a record ID from an external system. 

Salesforce field setting of external ID

Salesforce field setting of external ID

External IDs are searchable in Salesforce (as compared to the Salesforce record ID, which is not searchable.) 

External IDs do not have to be set as unique, but this is not best practice. 

What is a Unique ID Field Setting?

Just what it sounds like, dummy. Just kidding just kidding. Don’t get all sensitive. 

To clarify, the ‘Unique ID’ field is a setting which prevents the same value from being used in multiple records for any specific field. The best practice when setting a field as an external ID is to also set it as a unique ID to prevent duplicates. You can set a unique ID as case sensitive or case insensitive. 

Salesforce field setting of unique case sensitive or unique case insensitive

Salesforce field setting of unique case sensitive or unique case insensitive

With this in mind, if you try to set a second record with an external ID that’s been set as unique and has already been set on another record, you will see the error “We hit a snag. Review the errors on this page. duplicate value found. Field__c duplicates value on record with id: xxxxxxxxxxxxxxx”

Salesforce duplicate record error message

Salesforce duplicate record error message

**Reminder for newbies. You can’t set an object’s record name as an external ID or a unique ID, as the object name is a standard text field that does not have settings for setting it as unique or external. You can’t use native validation rules to prevent creating duplicates based on record name. 

So, are there other options to prevent duplicates based on record name? Sure, but that’s a rabbit hole for another day.

Back to the task at hand…

How to Load a File in Data Loader using Upsert

Use Case: We have a list of accounts from Oracle, an external system, with the Oracle Id. We want to update the billing address of the Salesforce accounts that have a matching Oracle ID. When we do not find the Oracle Id in Salesforce, we want to insert new account records.

Step 1: Prepare your csv file to include the external identifier and the record information you want to update. 

Csv file prepared for Salesforce data loading upsert

Csv file prepared for Salesforce data loading upsert

Step 2: Ensure the Salesforce field that contains the external ID referenced by your file has been set as an External ID. 

Setting the custom Salesforce field as an external ID

Setting the custom Salesforce field as an external ID

Step 3: Login to the data loader and select UPSERT as your action.

Salesforce data loader UPSERT action

Salesforce data loader UPSERT action

** When using Dataloader.io, select IMPORT from NEW TASK and then choose UPSERT as the operation.

Salesforce Dataloader.io new task import button
Salesforce Dataloader.io new task import button
Salesforce Dataloader.io upsert operation
Salesforce Dataloader.io upsert operation

Step 4: Select your object and your file, and proceed to mapping.

Salesforce data loader file selection

Salesforce data loader file selection

Step 5: Before you can map your fields in an upsert action, you have to select the field for matching. 

Salesforce data loader upsert field for matching

Salesforce data loader upsert field for matching

Only applicable fields will appear in the dropdown, i.e., the record ID and any fields that have been marked as external IDs. 

Salesforce data loader upsert only external IDs and the record ID appear

Only external IDs and the record ID appear

In dataloader.io, the smart mapping kicks in and selects the right field. 

Salesforce dataloader.io upsert setting the external ID

Salesforce dataloader.io upsert setting the external ID

Step 6: Before proceeding to the field mapping, you can also select external IDs on related objects for matching. If this isn’t applicable, you can leave this blank. 

Salesforce data loader upsert related object external IDs

Match on related object external IDs

Step 7: Complete your field mapping

Salesforce data loader upsert field mapping

Salesforce data loader upsert field mapping

Step 8: Select a place for your output files if using data loader. See this post for details and best practices. If using dataloader.io, this isn’t applicable. 

Salesforce data loader select folder location for saved success and error files

Select folder location for saved success and error files

Step 9: Click the Finish button. You will see the alert message that says “You have chosen to add new records and/or update existing records. The action you are about to take cannot be undone. Are you sure you want to proceed?” 

Click yes. 

Salesforce data loader upsert operation alert message

Salesforce data loader upsert operation alert message

Step 10: Operation status message pops up. 

Salesforce data loader operation finished message

Salesforce data loader operation finished message

Step 11: Open your success and error files.

Salesforce data loader saved success and error files

Salesforce data loader saved success and error files

Step 12: Review your files. 

In our sample load, we had 6 successful upserts. The success file shows which records were inserted versus which were updated in the status column. (The status column was not part of our originally prepared file – data loader added it to the success file.)

The Salesforce record ID has been added to the success file by the data loader with the record IDs of what was updated as well as what was created. 

Salesforce data loader success file record status and record ID

Salesforce data loader success file record status and record ID

Step 13: Check Salesforce and cross reference several of the records to ensure the addresses were updated as expected. 

Here’s a screenshot of our original Salesforce data before the upsert load. 

Pre-UPSERT Salesforce records

Pre-UPSERT Salesforce records

Here’s a screenshot of our data after the upsert load. 

Post-UPSERT Salesforce records

Post-UPSERT Salesforce records

Notice the updated address of the Critically Endangered Aardvark Center. Also note that there’s a new account called Critically Endangered African Wild Dog Foundation. 

Conclusion

In essence, use the UPSERT operation when you have a list of records that have an external ID and you’d like to update existing records and insert new records. 

Post main image credit to Dominik Vanyi on Unsplash. I edited the image in Canva because I am a creative genius. 

Search

Table of Contents

Woman with orange fingernails subscribing to Salesforce tutorials
Don't miss out!

weekly tutorials

I’ll send you a new Salesforce tutorial every week, because that’s just the kind of gal I am.