Performing UPSERT Operation

Skyvia supports UPSERT operation in data import for all the supported cloud applications and relational databases, except for Google BigQuery and Amazon Redshift. UPSERT operation requires the primary key to be auto-generated.

ExpandedToggleIcon        What is UPSERT

The UPSERT operation updates a record if it exists or inserts a new record. This allows you to avoid inserting duplicate data. You need to map the target ID/Primary key columns for performing UPSERT.

In Skyvia, UPSERT determines what action to perform in the following way: if a Null value is specified for the ID or primary key, UPSERT operation inserts the record, and if a non-null value is specified, UPSERT operation tries to update the record with the specified ID or primary key. Skyvia does not actually checks if such record exists, and providing invalid ID/PK values results in failed records.

 

ExpandedToggleIcon        What if ID/Primary Key Values Are Unknown?

If you import CSV files, you can get the necessary values using Export or Query. However, it's often not an option, especially when you import data from a database or cloud application directly, so Skyvia provides a more convenient way, and you can perform UPSERT even without knowing the ID values. You can use Lookup Mapping for ID/PK columns and get the IDs or PK values from the target object itself by some other field that uniquely identifies a record.

note Note

When using lookup mapping for ID or PK columns in UPSERT, don't forget to select the Set null when no match found check box in Lookup Options. Otherwise, lookup will produce errors if no such record found, and there would be failed records instead of inserted new ones.

 

ExpandedToggleIcon        How to Configure UPSERT

In order to create an UPSERT import task, specify source settings as you need. Then, in target settings, select the target object and click Upsert. After this, in Mapping settings, map the target columns. Primary key columns must be mapped, they are marked as Required for UPSERT. If you have the corresponding primary key values in your source, map PK columns to the corresponding source columns using Column Mapping, and make sure that for records you want to perform INSERT for, there are NULL values in these columns. Otherwise, you can use Lookup Mapping.

 

ExpandedToggleIcon        UPSERT in Salesforce

Unlike other data sources, Salesforce offers native support for UPSERT. Salesforce performs UPSERT by an External ID field. When you perform UPSERT to a Salesforce object with an External ID field in Skyvia, you can choose whether to use the ID field, as described above, or External ID. If you select Use External ID, you need to select the External ID field to use, since a Salesforce object can have more than one custom External ID field.

upsert-external-id

When using External ID, you don't need to map the ID field. Instead you need to map the selected External ID field, which is mapped as Required on the Mapping Definition page.

 

ExpandedToggleIcon        Example: Upserting Salesforce Contacts from MailChimp

Suppose we need to import subscribers from a mailing list in MailChimp to Salesforce contacts, and part of the corresponding contacts are already present in Salesforce. We don't want to make duplicate records, so we perform UPSERT instead of usual INSERT. So, to perform such an import, let's do the following:

 

Creating a Package

1.Click Integration in the menu on the left side of the page.
2.Click the New button in the top right corner of the page.
3.Click Import.
4.Click CSV in the Source header and select MailChimp from the list.
 
source-type-selection
5.Click Select in the Target header and select Salesforce from the list. You can use the Search box to quickly find the necessary source.
 
target-type-selection2
6.Select corresponding connections in the Source Connection and Target Connection lists. In order to shorten this example, let's assume, we already have the connections created. If not, see the Connecting to Salesforce and Connecting to MailChimp topics.
7.Click + Add Task.

 

Configuring Source

When configuring the source data, we need not only to select the source table, but also to configure data filtering in order to import subscribers only from one of the lists. In our example, the name of this list is "Test list".

1.In the Object list select ListMembers.
2.Click Add Condition.
3.In the first (leftmost) list of the condition, select Lists.
4.In the second list of the condition, select Name.
5.Enter "Test list" to the rightmost box of the condition.
6.Click Next.
 
import-listmembers-upsert-source

 

Configuring Destination

1.In the Target list, select Contact.
2.Click Upsert.
3.Click Next.
 
import-listmembers-upsert-target

 

Configuring Mapping

First we need to map the Id column of Salesforce Contact. As we can see, it is automatically mapped to the Id column of the MailChimp ListMembers table, because these columns have the same name. However, Salesforce and MailChimp Ids have different format and are completely different things, so we cannot map Salesforce Ids to MailChimp Ids. We will use the lookup mapping to map the target Id field, and determine the necessary Contact Ids by email.

1.Click Id.
2.Click Column and then select Target Lookup on the list.
 
mapping-select-kind
3.In the Lookup Object list, select Contact.
4.In the Result Column list, select Id.
5.In the Lookup Key Column list, select the Email target column.
6.In the bottom drop-down list, select the Email source column.
7.Click Options.
8.Select the Set null when no match found check box
 
import-listmembers-mapping-id
 
9.Click the LastName column and map it to the Last Name source column using column mapping.
10. In the same way map the FirstName target column to the First Name source column.
11. Click Finish.
12. Click Save.

Now our package is ready, and you can execute it.