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

Available SQL actions

By default, Etlworks inserts records into the destination table.

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

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

UPDATE, DELETE, MERGE, BULK MERGE and BULK DELETE/INSERT, 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 Etlworks checks to see if the record already exists. If it does, Etlworks 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.