Skip to main content
The Upsert action updates or inserts CData data. By default, if a record already exists in CData, an update is performed on the existing data in CData using the values provided from the input. See UPSERT Query Behavior for more information.
The CData connector is deprecated. Unlike the other database connectors, it does not support Mapping mode, so an XML Map connector is still required to map columns for the Upsert action.

​
Tables and Columns

If you choose Upsert for the CData connector, you must select a target table (or tables) from CData. Click the Add button above the Tables pane. The Upsert Configuration panel showing the Tables section with an Add button. A modal appears and lists all available tables. Select the desired table and click Add. The Add Table modal listing available tables, with the Orders table selected and the Add button at the bottom. The chosen table appears under the Tables pane, and the columns in the table appear under the Columns pane. The connector automatically detects values that have special relevance (such as the primary key and foreign keys) and labels them accordingly. The Upsert Configuration panel with the Orders table selected, showing the Columns pane with the Id column's UPSERT toggle set to ON and the UPSERT by field set to Id. By default, all columns are selected for inclusion in the action for the CData connector. Exclude individual columns by unchecking them.
You can select more tables by clicking Add and repeating this process. See Child Tables for more information.

​
UPSERT Query Behavior

By default, the UPSERT slider in the column mapping is set to ON. This means the UPSERT mapping determines whether a record exists in the destination before INSERTing it (if there is no matching record) or UPDATEing it (if the record already exists). The Upsert Configuration panel with the UPSERT ON toggle highlighted on the Id column. The query that is used to determine that value is determined by the logic described in the following sections.

​
UPSERT Slider Set to ON

When the slider is set to ON, the query behavior changes based on what you select in the UPSERT By and Perform this query… radio buttons.

​
UPSERT By Id

If the key column is present in the input XML, a query is issued to the database to determine if any records exist with the matching key:
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

A query is issued to the database to determine if any records exist where the selected UPSERT By column contains the matching value, by selecting the primary key of the 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

You can also write a custom query to select the UPSERT key. This option allows for the lookup to be performed using more complex logic than just 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 Slider Set to OFF

When the slider is set to OFF, the connector simply inserts all queries into the database. 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

After you choose the tables and columns for the Upsert-configured CData connector, place the connector at the end of an Arc flow. The screenshot below depicts an example flow with a File connector, a CSV connector, an XML Map connector, and a CData Upsert connector at the end: An example Arc flow showing four connectors in sequence: File_Receive, CSV1, XMLMap1, and CData_Upsert. Data in this example flow passes through these steps:
  1. The File connector pulls a CSV file from disk and into the flow.
  2. The CSV1 connector translates the CSV file into XML.
  3. This data passes to the XML_Map connector as the Source File, which maps to the Source tree.
  4. Data from the CData_Upsert connector passes to the XML_Map connector as the Destination File, which maps to the Destination tree. The XML Map connector's Mapping interface showing the Source tree (source.xml) on the left and the Destination tree (destination.xml) on the right, with no mappings yet drawn between elements.
  5. The XML_Map connector attempts to automatically map Source elements to Destination elements with the same name. You can manually change these and fill in blank mappings by dragging elements from Source to Destination. The XML Map connector's Mapping interface with elements from the Source tree mapped to the Destination tree, shown as curved lines connecting fields such as first to firstName and last to lastName.
  6. The CData_Upsert connector performs the Upsert according to the mapping and passes the resulting data to CData.