Perform a bulk upsert to a database in Jitterbit Studio

Introduction

A Database Upsert activity writes records to a database by inserting new ones and updating existing ones in a single pass, based on one or more key fields you specify. Because the operation handles both cases together, it can run repeatedly against the same dataset without creating duplicates, which makes it the right choice for both initial data migrations and ongoing sync operations.

If you only need to insert new records, use a Database Insert activity instead. If you only need to update existing records, use a Database Update activity.

For large datasets, Studio supports chunking: each chunk of records is committed to the database as it becomes available rather than waiting for all records to be processed. This reduces memory usage and can significantly improve throughput. Chunking is covered in Part 3.

This guide assumes a Database connection is already configured in the project.

Design pattern

The Upsert activity follows the transformation pattern:

Part 1: Configure the Database Upsert activity

Step 1: Name the activity and select a table

1. On the design canvas, drag a Upsert activity type from an existing Database connection onto the canvas.

2. Double-click the activity to open its configuration.

3. Name: Enter a descriptive name, such as Upsert Contacts.

4. Click the refresh icon to load available tables. For databases with a large number of tables, use the View dropdown to filter by type or enter a search term in the Search box before refreshing.

5. In the Provide the table names reference list, click the target table to select it. The selected table appears in the panel on the right.

6. Allow truncation of character fields to avoid field length errors: Leave this checkbox unselected if you want field length mismatches to surface as errors. If it is unselected and oversized data arrives, the operation returns an Error status. If it is selected, the operation returns Success with Info and the operation logs record that one or more values were truncated.

7. Include the schema/owner in the table names: Select this checkbox if the same table name exists in more than one schema, so that Studio can distinguish between them.

8. Click Next.

Step 2: Select upsert keys

1. In the Select keys panel, select the checkbox for each field that uniquely identifies an existing record.

   Tip

   Use a natural business key (for example, an email address or external_id field assigned by the source system) rather than a database-generated primary key. A generated key is not known until after the first insert, so it cannot be used to match records on subsequent runs.

   The selected fields appear in the right-hand panel as the active upsert keys.

2. Click Next.

Step 3: Review the data schema

1. Review the target data schema. This schema is used in the transformation in Part 2.

2. Click Finished.

Part 2: Map the transformation

1. On the design canvas, add a Transformation step between the source activity and the Database Upsert activity.

2. Open the transformation. The source schema appears on the left and the Upsert target schema appears on the right.

3. When the target schema is mirrored directly from a single-table Upsert activity, the target includes a row node directly under the root. Studio automatically treats this node as a loop node so that each record in the source produces one upsert row in the database.

4. Map source fields to the corresponding target fields under the row node.

   Note

   For multi-table (joined) targets, the values of a child table's joined fields default to the corresponding parent table field values. Child table joined fields cannot be mapped directly in the transformation.

For a full walkthrough of transformation mapping, see Transformation mapping overview.

Part 3: Improve performance for large datasets

By default, Studio accumulates all processed records and writes them to the database in a single operation. For large datasets, enabling chunking causes each chunk to be committed as it becomes available, which reduces peak memory usage and can significantly improve throughput.

To enable chunking, set the Jitterbit variable jitterbit.target.db.commit_chunks to true or 1. Do this in a script step that runs before the upsert operation, or configure it directly in the operation settings.

To set it in a script:

$jitterbit.target.db.commit_chunks = true;

To configure chunk size and enable chunking through the operation settings UI, see Operation options.

Verify the integration

Deploy and run the operation. Check the operation logs to confirm the Upsert activity completed successfully and verify that the record count in the database matches the expected total from the source.

If the operation returns an Error status due to field length mismatches, either select Allow truncation of character fields in the activity configuration (Step 1) or use String functions to trim values in the transformation before they reach the target.