Using MERGE (UPSERT) when working with databases

Overview

In the Etlworks Integrator, you can use MERGE (UPSERT) or the IfExist Action when working with the database.

MERGE

When creating a Flow that loads data into the relational database and you want to INSERT new records and UPDATE the existing ones, use the built-in MERGE SQL action.

The Etlworks 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.

Configure MERGE

Follow the steps below to configure 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 PostgreSQL require a unique index for the Lookup Fields.

Predict Lookup Fields with MERGE

You can enable the Predict Lookup Fields as an alternative to configuring the Lookup Fields. It is only recommended 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 Fields. 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, the Etlworks 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, the Etlworks Integrator will execute an INSERT.

Use IfExist if:

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

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

Configure IfExists

Here's how you can configure IfExists:

Step 1. Create a Flow that 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

You can enable the Predict Lookup Fields as an alternative to configuring the 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 IfExist 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 Fields. Use the fully-qualified table names and ; as a separator between table=field pairs.

An alternative to enabling the Predict Lookup Fields option (which is not always accurate) is to specify the list of table=fields pairs in the Lookup Fields. 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 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.

Configure CUSTOM UPSERT

To configure CUSTOM UPSERT, apply the following steps:

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 the 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