Using MERGE (UPSERT) when working with databases

MERGE

If when creating a flow that loads data into the relational database you want to INSERT new records, but UPDATE the existing ones, use the built-in MERGE SQL action.

Integrator supports MERGE for Oracle, MS SQL Server, PostgreSQL, MySQL, DB2, Informix, and Sybase databases. If your database is not on the list or if you don't want to use MERGE, read how to configure the IfExist action.

Configuring MERGE

Step 1. Create a flow with at least one transformation where the database is a destination.

Step 2. When configuring a transformation, click MAPPING and then select the Parameters tab.

Step 3. Select MERGE as the Action.

Step 4. Enter a comma-separated list of field names in the Lookup Fields which will be used to identify a unique record. 

MySQL and PostrgeSQL requires a unique index for the lookup fields.

Predict Lookup Fields with MERGE

Alternatively to configuring the Look Field(s) you can enable the Predict Lookup Fields. It is recommended only if you are extracting data from the database objects matching the wildcard name.

Alternative to Predict Lookup Fields with MERGE when processing by a wildcard

When using the wildcard processing with MERGE and enabling the Predict Lookup Fields option (which is not always accurate) is not an option you can specify the list of table=fields pairs in the Lookup Field. Use the fully-qualified table names and ';' as a separator between table=field pairs.

Example:

test1.inventory=inventory_id,database_name;
test1.payment=payment_id,database_name;
test1.rental=rental_id,database_name;

IfExists

The alternative to MERGE is the IfExist action. Instead of relying on the native MERGE, which is included in the target database engine, Integrator will execute an SQL statement to check to see if the record already exists, and if it does, will then execute an UPDATE. If the record does not exist, Integrator will execute an INSERT.

Use IfExist if:

• the native MERGE is not supported by the destination database, or
• you cannot create an index for the fields used to identify a unique record, and the destination database is either PostgreSQL or MySQL, or
• you just don't want to use the native MERGE action, for whatever reason.

IfExist is much slower than MERGE so use MERGE when possible. 

Configuring IfExists

Step 1. Create a flow which includes at least one transformation where a database is the destination.

Step 2. When configuring that transformation, click MAPPING and then select the Parameters tab.

Step 3. Select IfExist as the action.

Step 4. Enter a comma-separated list of field names that will be used to identify a unique record.

Predict Lookup Fields with IfExists

Alternatively to configuring the Look Field(s) you enable the Predict Lookup Fields. It is recommended only if you are extracting data from the database objects matching the wildcard name. 

Alternative to Predict Lookup Fields with IfExists when processing by a wildcard

When using the wildcard processing with IfExists and enabling the Predict Lookup Fields option (which is not always accurate) is not an option you can specify the list of table=fields pairs in the Lookup Field. Use the fully-qualified table names and ';' as a separator between table=field pairs.

Alternatively to enabling the Predict Lookup Fields option (which is not always accurate) you can specify the list of table=fields pairs in the Lookup Field. Use the fully-qualified table names and ';' as a separator between table=field pairs.

Example:

test1.inventory=inventory_id,database_name;
test1.payment=payment_id,database_name;
test1.rental=rental_id,database_name;

CUSTOM UPSERT

Some database connectors support the UPSERT SQL clause, which looks very much like INSERT: UPSERT INTO table (columns) VALUES (values). But the actual syntax of the UPSERT very much differs depending on the driver. With the CUSTOM action, the developer can provide a template for UPSERT which will be used at runtime to generate the actual SQL statement.

At this time only some of our premium connectors support the UPSERT SQL clause, for example, our Salesforce with OAuth2 connector.

Configuring CUSTOM UPSERT

Step 1. Create a flow with at least one transformation where the database is a destination.

Step 2. When configuring a transformation, click MAPPING and then select the Parameters tab.

Step 3. Select CUSTOM as the Action

Step 4. Enter a comma-separated list of field names in the Lookup Fields which will be used to identify a unique record. Alternatively, you can enable the Predict Lookup fields. 

Step 5. Enter template for the UPSERT into the Destination query field.

Example:

UPSERT INTO LocationToUse ({COLUMNS}, ExternalIdColumn) 
VALUES ({VALUES}, '{KEYS}')

The following tokens can be used in the Destination query:

• {COLUMNS} - the comma-separated list of columns
• {VALUES} - the values for the columns
• {KEYS} - the lookup fields