Using MERGE (UPSERT) when working with databases

MERGE

If when creating a flow which 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 and 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 and enter a comma-separated list of field names which 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;