You may be wondering that there are tons of articles available for upsert operation and what is need of one more blog post. I wanted to note everything important about upsert operations using datalaoder and Apex with identified gotchas at single place, and that’s why its here 🙂 .
External ID is field in Salesforce to say that this field is primary key in external Database which can be used to identify that external record exists in Salesforce or not ? There are many benefits of external key like :
- It can be used to identify if record exists or not and record automatically inserted or updated using upsert operation
- Upsert operation is supported by Dataloader, Apex and API
- Any field marked as external id is indexed and can be used in SOQL or report filter
- You can create Parent child record in single statement in Apex
Using Upsert operation to create/update child record and relate to existing parent record with help of External Id
Lets assume that you have list of contacts and Account in external SQL Server database and you want to import those Account and contact weekly. It is possible that you have already imported contacts previously so don’t want to duplicate records if already created in Salesforce. To solve this problem we can use external Id on Contact and use upsert operation on dataloader. Let’s assume external Id field on Contact is “SQL_Server__Id__c” (I am assuming that Account records in Salesforce also have external Id and records are already imported. Same below steps can be used to import Account record as well).