Articles in this section

See more

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

  • Updated
Follow

When the destination of a flow is a database, Etlworks writes each record using a configurable SQL Action. The action defines how a source row is applied to the destination table.

The default action is INSERT. You can change it to:

  • INSERT
  • UPDATE
  • DELETE
  • MERGE (UPSERT)
  • IFEXIST
  • CUSTOM
  • Conditional

How do I configure the SQL Action?

  1. Open the transformation.
  2. Click Mapping.
  3. Select the Parameters tab.
  4. Set Action.

SQL Action parameter

What are Lookup Fields and when do I need them?

Lookup Fields identify the destination record that should be updated or deleted. They are required for UPDATE, DELETE, MERGE (UPSERT), IFEXIST, CUSTOM, and Conditional.

You can enter the lookup fields manually or have Etlworks determine them automatically by enabling Predict Lookup Fields.

Lookup Fields

What each action does

INSERT

Adds a new record to the destination table for each source record. This is the default.

UPDATE

Modifies existing records. The destination record is identified by the Lookup Fields. If no match is found, nothing happens.

DELETE

Removes records identified by the Lookup Fields.

MERGE (UPSERT)

Updates the record if it exists, inserts it otherwise. Natively supported on Oracle, MS SQL Server, PostgreSQL, MySQL, DB2, Informix, and Sybase. For details and the faster BULK MERGE / BULK DELETE/INSERT variants, see the section MERGE, BULK MERGE, BULK DELETE/INSERT, and IFEXIST in depth below.

IFEXIST

Alternative to MERGE for databases that don't support native MERGE. Etlworks issues a SELECT to check whether a matching record exists, then runs UPDATE or INSERT.

IFEXIST is significantly slower than MERGE / BULK MERGE / BULK DELETE-INSERT. Prefer those when the database supports them.

CUSTOM

Defines a custom SQL template that runs for each record. Source fields are referenced as {field_name} and become bind variables at runtime.

CUSTOM SQL supports complex statements, multiple operations, temporary tables, database-specific syntax, and writing one source record to multiple destination tables. See Advanced SQL with the CUSTOM action for the full reference.

CUSTOM UPSERT

A special form of CUSTOM for connectors with a non-standard UPSERT syntax (for example, Salesforce). The template uses the tokens {COLUMNS}, {VALUES}, and {KEYS} instead of named field tokens. Example:

UPSERT INTO LocationToUse ({COLUMNS}, ExternalIdColumn)
VALUES ({VALUES}, '{KEYS}')

See CUSTOM UPSERT.

Conditional

Runs different SQL actions based on a JavaScript condition — for example, INSERT when a flag is set and DELETE when it is not. See Execute INSERT / UPDATE / DELETE / MERGE conditionally.

MERGE, BULK MERGE, BULK DELETE/INSERT, and IFEXIST in depth

MERGE

Per-record UPSERT executed once per source row. MERGE can be slow on large volumes; use BULK MERGE or BULK DELETE/INSERT when possible.

To configure MERGE:

  1. Create a flow with at least one transformation where the database is the destination.
  2. Open the transformation, click MAPPING, and select the Parameters tab.
  3. Set Action to MERGE.
  4. Enter a comma-separated list of field names in Lookup Fields. These identify a unique record.

MERGE action

Predict Lookup Fields with MERGE

Enable Predict Lookup Fields as an alternative to entering them manually. Recommended only when extracting data from objects matching a wildcard name.

Per-table lookup fields when processing by wildcard

When Predict Lookup Fields is not accurate enough, specify a list of table=fields pairs separated by ;. Use fully qualified table names:

test1.inventory=inventory_id,database_name;
test1.payment=payment_id,database_name;
test1.rental=rental_id,database_name;

MERGE with PostgreSQL and MySQL

PostgreSQL before 15.0 and MySQL require a unique index on the Lookup Fields. PostgreSQL 15+ supports native MERGE and does not require a unique index.

On PostgreSQL 15 and 16, native MERGE can be slower than INSERT ON CONFLICT. To use INSERT ON CONFLICT instead, enable Use INSERT ON CONFLICT with Postgres 15+ on the PostgreSQL connection.

BULK MERGE

BULK MERGE creates a temporary table in the destination database, inserts all records into it, and then runs a single MERGE between the temporary table and the actual table.

Pros: can be up to 100x faster than per-record MERGE.

Cons:

  • The database must support temporary tables.
  • Not supported on PostgreSQL before 15.0 and on MySQL. For these, use BULK DELETE/INSERT.

To configure: set Action to BULK MERGE. Lookup-field configuration is the same as for MERGE.

BULK DELETE/INSERT

Similar to BULK MERGE but uses DELETE FROM actual_table WHERE EXISTS (SELECT 1 FROM temp_table WHERE conditions) followed by INSERT INTO actual_table SELECT FROM temp_table instead of a native MERGE.

Use BULK DELETE/INSERT for databases that don't support native MERGE — for example, PostgreSQL before 15.0 and MySQL.

To configure: set Action to BULK DELETE/INSERT. Lookup-field configuration is the same as for MERGE.

IFEXIST

IFEXIST runs a SELECT to check whether the record exists, then UPDATE or INSERT accordingly. It is much slower than MERGE / BULK MERGE / BULK DELETE-INSERT — prefer those when the database supports them.

To configure: set Action to IFEXIST. Lookup-field configuration is the same as for MERGE.

CUSTOM SQL

CUSTOM is the most flexible action. It runs a custom SQL template (with bind-variable tokens) for each record. See Advanced SQL with the CUSTOM action for full coverage, including multi-statement examples and CUSTOM UPSERT.

Related articles