SQL actions: INSERT, UPDATE, DELETE, MERGE, IFEXIST, CUSTOM

Available SQL actions

By default, the Etlworks Integrator inserts records into the destination table.

You can change this to UPDATE, DELETE, MERGE (UPSERT), IfExist, CUSTOM, or Conditional. 

To change the action go to Mappings > Parameters > Action. 

UPDATE, DELETE, MERGE, IfExist, CUSTOM, and Conditional actions require setting the Lookup fields.

Read about SQL action and Lookup Field(s) parameters.

MERGE

MERGE is natively supported for Oracle, MS SQL Server, PostgreSQL, MySQL, DB2, Informix, and Sybase databases.

Read how to use MERGE.

IFEXIST

The alternative to MERGE is the IfExist action where the Etlworks Integrator checks to see if the record already exists. If it does, the Etlworks Integrator will execute UPDATE; otherwise, it will execute INSERT.

Read how to use IfExists.

CUSTOM UPSERT

Another alternative to MERGE is the CUSTOM action. Some database connectors support the UPSERT SQL clause, which looks very much like INSERT: UPSERT INTO table (columns) VALUES (values). However, 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.    

Read how to use CUSTOM UPSERT action.

Conditional action

The Action can require entering JavaScript conditions into the Action Conditions fields. 

Read how to execute SQL action conditionally.