Skip to main content

​
Tables and Columns

​
UPSERT Query Behavior

UPSERT settings are configured per column in both Table Structure and Mapping modes. Right-click a column and select UPSERT Settings from the context menu.

The UPSERT Settings dialog opens. By default, the Enable UPSERT settings toggle is ON, meaning the connector checks whether a record already exists in the destination before deciding to INSERT or UPDATE it.

The query used to determine whether a record exists is controlled by the logic described in the following sections.

​
UPSERT Settings Enabled

When Enable UPSERT settings is on, query behavior depends on the option selected in the UPSERT Settings list: UPSERT by (match on a single column) or Query to select UPSERT key (use a custom lookup query).

​
UPSERT By Id

When you select UPSERT by in the UPSERT Settings list and choose the table’s key column, the connector issues a query to determine if any records exist with the matching key value:
A match results in an update; no match results in an insert. If the key column is not present in the input XML, the record is always inserted:

​
UPSERT By Non-key Column

When you select UPSERT by in the UPSERT Settings list and choose a non-key column, the connector issues a query to determine if any records exist where that column contains the matching value, targeting the primary key in the SELECT (the following examples illustrate UPSERTing to an Orders table):
The SELECT query always targets the key column(s) of the table in the SELECT statement. If there is a successful match, the data in the input XML is updated into the data source. If there is no match, the record is inserted instead.
If the selected UPSERT by column is not specified in the input, the lookup is used with a NULL value, as shown in the following query:

​
Using a Custom Query

When you select Query to select UPSERT key in the UPSERT Settings list, you can write a custom query to determine the UPSERT key. This allows the lookup to use more complex logic than a single column match. Consider the following query:
In this example, you must select the key column(s) of the table, to provide adequate criteria for the UPDATE. The @OrderID syntax is used to reference a value from the matching input XML. You can also use static values: the quotes around 'Active' denote that this clause uses a static value. Again, a successful match of the key column is used to form an update request. If there is no match, the record is inserted.
When in doubt about the behavior of a database insertion, review the .log file for the UPSERT attempt: a complete breakdown of the logic used to guide the behavior is sent to the log file.

​
UPSERT Settings Disabled

To disable UPSERT behavior for a column, open the UPSERT Settings dialog (right-click the destination column and select UPSERT Settings), toggle Enable UPSERT settings to OFF, and click Apply. When UPSERT settings are disabled, the connector issues only INSERT queries. Examining the code view of the input mapping shows that the action attribute of the table is insert:
All queries issued to the table are treated as inserts, whether or not the primary key is present. To have all received records updated against the table, you can set this action keyword to update, as shown in the following snippet:
When the action is defined as update, the application expects the key column of the table (for example, Id) to be present. If the key column is not provided, the effects of the UPDATE command can vary by target database type, so you might see more rows changed than expected, or that none are changed at all.

​
Example Arc Flow