Upsert

Upserts (Creates or updates records) in a Relational Database.

Usage

You can view the list of supported databases and how to configure them in the Project Editor documentation. There is a separate Upsert Node in the Node Selector for each database technology supported in Sanbox.

Configuring Upsert Node

Configuring a Connection

The Upsert Node takes a connection object and tells it how to communicate with the database. You can configure Project level connections in the Project Editor.

How To Upsert Data

The Upsert Node takes an array or single object from the Data conduit and creates/modifies records in the database. It maps each object(s) properties to the columns in the configured Table Name. Your Table Name may contain a schema as well. Properties not found as columns are ignored.

Updating properties with their values set to null (No Value source in conduit) will update the mapped column in the database to null as well. You can upsert an object containing only the properties you want to update to avoid problems.

Updating & Creating

  • Checking Update Records tells the Upsert Node to update existing objects. Checking this requires you to specify one or more Primary Key(s) separated by semi-colons.

  • Checking Create Records tells the Upsert Node to create all objects in the Data conduit.

  • Checking both Create Records and Update Records tells the Upsert Node to update a record if it exists by key and creating it otherwise.

When the Upsert Node finishes, it outputs the number of records created/modified.