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.

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.

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.

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.

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.

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.