Using Salesforce Data Loader Upsert: Comprehensive Guide 101

Salesforce is a robust Customer Relationship Management (CRM) platform powered by Customer 360. It further offers a complementary suite of enterprise applications that focus on Marketing Automation, Data Analytics, Customer Service, and Application Development. Simply put, Data Upsert is a Database Management System (DBMS) feature that lets a DML statement’s author either insert a row atomically or based on the existing row, UPDATE that existing row instead, without having to worry about concurrency.

This article talks in detail about Data Loader Upsert in Salesforce through an example, eliciting the points required to set it up for your workflow. It also gives a brief introduction to Salesforce and the concept of Data Upsert before diving into Salesforce Data Loader Upsert.

Table of Contents

• What is Salesforce?
  • Key Features of Salesforce
• What is Data Upsert?
• Understanding Data Loader Upsert Example
  • Salesforce Data Loader Upsert: What is Record ID in Salesforce?
  • Salesforce Data Loader Upsert: Inserting and Updating a Record using Data Loader Upsert
  • Salesforce Data Loader Upsert: Mapping Fields
  • Salesforce Data Loader Upsert: Validating the Operation
• Conclusion

What is Salesforce?

Salesforce serves as an integrated CRM (Customer Relationship Management) platform that provides a single, shared view across all your departments, including Sales, Service, Commerce, and Marketing. Salesforce can help you empower and develop your Sales teams with integrated Lead Management, Forecasting Tools, and the ability to Quote, Price, and Configure every deal. In addition, Salesforce provides a comprehensive Cloud platform (Sales Cloud) that gives you the Customer Management Solution that is the right fit for every growth phase of your company.

Salesforce Cloud is an all-in-one Sales tool that also doubles as CRM software. Salesforce Cloud is flexible enough to accommodate organizations of any size or industry and scalable for anything you might need from it. In addition, it allows you to access data on the go through its mobile app. It can also help maximize Sales with AppExchange-based integrations, thus boosting business growth.

Key Features of Salesforce

• It allows you to design mobile-friendly Salesforce apps for every employee. This helps boost productivity and build a more connected workforce. Apart from this, it also enables you to streamline the employee help desk for faster self-service. 
• Salesforce offers a boost on its core services with additional functionalities such as Application Integration and Artificial Intelligence. This includes building custom-branded mobile apps, easy integration of data from ERPs, IoT, and Databases, and addition of predictive capabilities to every Service and Sales application.
• You can make your apps more personalized and intelligent to meet your needs in real-time engagement, service, and purchase.
• Salesforce provides a highly curated library of industry-specific expert help. This includes Process Flows, Apps, Templates, and Components built to tackle every single issue faced by the user.

Salesforce CRM further ensures a personalized experience with its Customer 360 Platform. Some of the key products offered by Salesforce as a part of their suite are as follows:

• Salesforce Service Cloud: Salesforce Service Cloud offers all the tools you might need to strengthen Customer Engagement. It allows you to automate business processes with Intelligent Workflows, enable Customer Interactions across each channel, get Actionable Insight into each Customer Interaction, and Drive Revenue with the power of Artificial Intelligence. 
• Salesforce Financial Services Cloud: Salesforce Financial Services Cloud gives you 360-degree visibility into the financial profiles of your clients for various sectors ranging from wealth management to retail banking. This helps you put your customer at the center of every interaction.
• Salesforce Analytics (Tableau CRM): Tableau allows you to leverage intuitive Drag-and-Drop Analysis at every skill level. It also allows you to quickly spot Visual Patterns and Share Insights across teams to drive growth. 
• Salesforce Einstein: This is an Artificial Intelligence technology developed by the Salesforce Customer Success Platform. The goal of this tool is to provide Marketing and Sales departments with more up-to-date views of Sales prospects and customers. 
• Salesforce Commerce Cloud: The Salesforce Commerce Cloud allows you to convert more customers with personalized buying experiences. You can implement your strategies faster with easy-to-use tools. This will enable you to stay agile and start generating a Higher ROI (Return on Investment) sooner. 
• Salesforce AppExchange: Salesforce AppExchange is an Enterprise Cloud marketplace. It allows you to find proven experts and apps to help you solve business challenges fast and extend your Salesforce Customer 360 from anywhere. This can be extended across any Department, Industry, and Product. It also offers app guides to help you zero in on the right apps to suit your unique business use cases and challenges.

What is Data Upsert?

A common question that comes to mind is the purpose of Data Upsert for your workflow. Here are a couple of reasons why you might want to execute Data Upserts on your dataset:

• Data Upserts allow you to avoid deadlocks that the user can’t avoid reasonably.
• With Data Upsert, you can guarantee insert-or-update atomicity for the simplest of use cases.
• It also allows you to avoid duplicate key violations that the implementation needs to trap and handle.

To illustrate the point further with an example, say you have a database with a table named employees along with an id column that serves as the primary key. Here’s what it can look like:

id	name	email
1	Jerome	jerome@whittaker.corp
2	Peter	peter@parker.corp

You can leverage an upsert when modifying employee information within this table. Logically, it would look something like this:

• If the employee ID exists in the table, this command will go ahead and update that row with the new information.
• If the employee ID doesn’t exist in the table, this command would just add it as a row.

Different RDBMS handle syntax for upserts differently, for instance, here’s the command you can use in MySQL:

INSERT INTO employees (id, name, email) VALUES (2, ‘Danny’, ‘danny@glover.corp’) ON DUPLICATE KEY UPDATE;

Here’s how the upsert command would look like in PostgreSQL:

INSERT INTO employees (id, name, email) 
VALUES (2, ‘Danny’, ‘danny@glover.corp’)
ON CONFLICT (id) DO UPDATE;

This is what this command would yield for both PostgreSQL and MySQL:

id	name	email
1	Jerome	jerome@whittaker.corp
2	Danny	danny@glover.corp

In this instance, the primary key value of 2 already exists in the table, so the upsert operation updated that row with the new values for email and name.

Similarly, if you run the following commands in MySQL and PostgreSQL:

INSERT INTO employees (id, name, email) VALUES (3, ‘Jeff’, ‘jeff@hardy.corp’) ON DUPLICATE KEY UPDATE;

INSERT INTO employees (id, name, email) 
VALUES (3, ‘Jeff’, ‘jeff@hardy.corp’)
ON CONFLICT (id) DO UPDATE;

This is what the outcome would look like:

id	name	email
1	Jerome	jerome@whittaker.corp
2	Peter	peter@parker.corp
3	Jeff	jeff@hardy.corp

Since there was no entry with the id 3 in the table, these commands would insert a new row into your table with that id.

Understanding Data Loader Upsert Example

Here are the steps involved in setting up Data Loader Upsert in Salesforce:

• Salesforce Data Loader Upsert: What is Record ID in Salesforce?
• Salesforce Data Loader Upsert: Inserting and Updating a Record using Data Loader Upsert
• Salesforce Data Loader Upsert: Mapping Fields
• Salesforce Data Loader Upsert: Validating the Operation

Here are a few prerequisites you must be mindful of before diving into Data Loader Upsert for Salesforce:

• Your CSV file should contain a column ID for matching against the current set of records. The column could either be the ID (the Salesforce record ID) or an external field (a custom field with the “External ID” attribute).
• If your file contains the external IDs of an object that has a relationship to your chosen subject, you should enable that external ID for record matching by choosing its name from the drop-down list. If you don’t make a selection here, you can leverage the related object’s ID field for matching by mapping it in the next step.

Salesforce Data Loader Upsert: What is Record ID in Salesforce?

• Step 1: Record ID is the distinct 15-digit number created for every record that is generated in Salesforce. To find the record ID in Salesforce User Interface, just go to any records and click on the URL as depicted below:

• Step 2: To update an existing record, you need the Record ID. However, you don’t need it to insert a new record into your table. You can use the CSV (Comma Separated Value) file to update a record by leveraging DataLoader in Salesforce.
• Step 3: You can create a CSV file as follows with the fields Course name, StudentInfo name, Subject 1, Fee paid, Subject 2,  Record ID, and Subject 3.

• Step 4: You can generate a CSV file as shown above and save it.
• Step 5: From the aforementioned CSV file, for this example, you need to insert Pink record in the Studentinfo_c object.
• Step 6: Coursename_c would be updated from B.Sc to M.Sc by leveraging the record ID. 

Salesforce Data Loader Upsert: Inserting and Updating a Record using Data Loader Upsert

• Step 1: Next, open up Data Loader in Salesforce and log in with your username and password.
• Step 2: Click on the Upsert function and click on the Next button.

• Step 3: You can then select the object from that list, and click browse to upload the CSV file which you created. Click on the Next button to finish this step.

• Step 4: With this, the initialization of the UPSERT operation has succeeded. You can then click on the OK button to start mapping fields.

Salesforce Data Loader Upsert: Mapping Fields

• Step 1: Next, you need to click on the Auto-Match fields to column.
• Step 2: You can then drag the Salesforce fields down to the column mapping. To get rid of mapping, you can select a row and click Delete.

• Step 3: Click on the Next button, this will show a warning window as follows:

• Step 4: Next, click on the Yes button to finish the step.

Salesforce Data Loader Upsert: Validating the Operation

• Step 1: As depicted above, the UPSERT operation was successful with the help of Salesforce DataLoader. To check the object, you first need to go to the Studentinfo_c object.

• As you can see, previously you didn’t have the Pink record in the Studentinfo_c object. But now, you can see that a new object has been created.

• As discussed in the UPSERT operation in DataLoader, both updating and inserting take place simultaneously. The Coursename_c in studentinfo named black gets updated to M.Sc from B.Sc automatically. 

Conclusion

This blog describes the steps involved in setting up Salesforce Data Loader Upsert after a brief introduction to the salient features and offerings of Salesforce. It also highlighted the concept of Data Upsert before delving into the Data Loader Upsert process setup for Salesforce.

Hevo Activate, a No-code Reverse ETL Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Activate with its strong integration with Google Sheets, Salesforce, Zendesk, etc., allows you to not only export data from your desired data warehouse & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.