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
Comments
0 comments
Please sign in to leave a comment.