Upsert Record
Description
The Upsert Record activity inserts a new record or updates an existing record in a specified database table based on configured unique columns.
The activity checks for an existing record that matches the values of the specified unique columns:
- If a matching record exists, the record is updated.
- If no matching record exists, a new record is inserted.
This provides a simple, UI-based way to synchronize individual records without requiring separate select, insert, and update activities.
Common settings
Use Process Data to bind supported inputs to workflow values or upstream outputs. See Common Activity Features for shared execution, error-handling, and output-persistence settings. Availability depends on the activity.
Design-Time Configuration
The following properties define how the activity connects to the database and performs the upsert operation.
Connection Type
Specifies the database engine (for example, SQL Server, Oracle, MySQL, PostgreSQL). Selecting the correct type ensures compatibility with the target database.
Connection Name
Select a preconfigured database connection that defines the server, credentials, and security settings.
Schema
Specifies the database schema that contains the target table.
Table Name
Specifies the database table in which the record will be inserted or updated.
Unique Columns
Specifies one or more columns used to determine whether a matching record already exists in the target table.
The activity compares the values of these columns with existing records:
- If a matching record is found, the existing record is updated.
- If no matching record is found, a new record is inserted.
Select columns that reliably identify a unique record in the target table. The values required for the selected unique columns must be available as part of the upsert operation.
Column Mapping
Defines how workflow values are mapped to columns in the target database table.
Use the Resource Mapper to map values from workflow data and activity outputs to the corresponding database columns.
The mapped values are used to identify the record using the configured Unique Columns and to insert or update the corresponding database values.
Ensure that the mapped values are compatible with the data types of the corresponding database columns.
Timeout
Specifies the maximum time (in seconds) that the activity will wait for the upsert operation to complete.
The default value is 600 seconds.
Outputs
Inserted Records Count
Returns the number of new records successfully inserted into the target database table.
For an Upsert Record operation, this value is typically 1 when a new record is inserted and 0 when an existing record is updated.
Updated Records Count
Returns the number of existing records successfully updated in the target database table.
For an Upsert Record operation, this value is typically 1 when an existing record is updated and 0 when a new record is inserted.
Execution settings
Configure Wait All Incoming when execution paths converge, and On Error for failure handling. Select Inherit where available to use the workflow-level error policy.
Example: Match a composite key
Select CustomerId and ExternalOrderId in Unique Columns and include both values in Column Mapping, together with the fields to insert or update. If the key already exists, the mapped non-key values are updated; otherwise a new row is inserted.
Choose non-null values that identify a unique database row and enforce that uniqueness in the database. Inspect Inserted Records Count and Updated Records Count to determine which operation occurred. A nonunique match can affect more than one existing row; the activity's name does not impose a database uniqueness constraint.