> ## Documentation Index
> Fetch the complete documentation index at: https://docs.arc.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite Upsert Configuration

> Insert or update records in a SQLite database from an Arc message in the flow.

The Upsert action updates or inserts SQLite data. By default, if a record already exists in SQLite, an update is performed on the existing data in SQLite using the values provided from the input. See [UPSERT Query Behavior](#upsert-query-behavior) for more information.

## Tables and Columns

The SQLite Upsert connector supports two configuration modes: **Table Structure** and **Mapping**.

**Table Structure** is the default. When the **Mapping** toggle is off, click **Add Table Structure** to select a target table from SQLite.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_landing_page.png)

On the **Edit Destination Structure** pane, select the desired table(s) and click **Apply**.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_table_modal.png)

> [!NOTE]
>
> See [Child Tables](https://docs.arc.cdata.com/26.4/self-hosted/en/connectors/sqlite/sqlite-ac.md#child-tables) for more information about selecting multiple related tables.

The selected table(s) and columns appear. The connector automatically detects values that have special relevance, such as the primary key, and labels them accordingly. Right-click a column to access additional settings, including UPSERT settings for that column.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_table_structure.png)

**Mapping** mode enables a full mapping editor. Enable the **Mapping** toggle to switch to this mode.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_mapping_editor.png)

In the **Destination** pane, click **Add Destination Structure**, select the target table(s), and click **Apply**.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_add_destination_modal.png)

The selected table(s) and columns appear in the **Destination** pane. Right-click a column to access additional settings, including UPSERT settings for that column.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_destination_structure.png)

> [!NOTE]
>
> A source template is not required to add a destination structure or configure UPSERT settings. You can set the UPSERT key and save in structure-only mode before connecting a source.

## 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.

![](https://docs.arc.cdata.com/26.4/images/dbsa_upsert_settings_dialog.png)

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:

```text theme={null}
The action type is UPSERT and an UPSERT query is not specified. The key column [Id] is present and will be determined using an automatically generated UPSERT query.
SELECT COUNT(*) AS [count] FROM [Orders] WHERE [Id] = @Id; @Id='123'
```

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:

```text theme={null}
The key column Id was not specified, INSERT will be executed.
The action type is UPSERT or an UPSERT query is specified. The key column is not present. Record will be 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):

```sql theme={null}
SELECT [Id] FROM [Orders] WHERE [OrderId] = @OrderId; @OrderId='12345'
```

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.

> [!NOTE]
>
> 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:

```sql theme={null}
SELECT [Id] FROM [Orders] WHERE [OrderId] = @OrderId; @OrderId=NULL
```

#### 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:

```sql theme={null}
SELECT [Id] FROM [Orders] WHERE [OrderId] = @OrderId AND Status = 'Active'
```

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.

> [!TIP]
>
> 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`:

```xml theme={null}
<Items>
    <Orders table="`Orders`" action="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:

```xml theme={null}
<Items>
    <Orders table="`Orders`" action="update">
```

> [!WARNING]
>
> 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 SQLite 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, and a SQLite Upsert connector at the end:

![](https://docs.arc.cdata.com/26.4/images/sqlite_upsert.png)

Data in this example flow passes through these steps:

<ol><li>The File connector pulls a CSV file from disk and into the flow.</li>
<li>The **CSV1** connector translates the CSV file into XML.</li>
<li>The **SQLite\_Upsert** connector receives the XML data. Configure column mapping directly in the connector on the **Action** tab using Mapping mode, as described in [Tables and Columns](#tables-and-columns). The connector then performs the Upsert and passes the resulting data to SQLite.</li></ol>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.