Articles in this section

See more

Advanced SQL with CUSTOM Action

  • Updated
Follow

Overview

When the destination of an ETL flow is a database, Etlworks controls how records are written using an SQL Action — INSERT, UPDATE, MERGE, DELETE, IFEXIST, or CUSTOM.

The CUSTOM action is the most flexible. It runs a custom SQL template for each processed record.

Earlier versions of Etlworks supported a limited CUSTOM pattern designed primarily for simple UPSERT statements. The current CUSTOM action supports complex SQL statements and multi-step operations, so you can implement advanced database logic directly inside a transformation:

  • Write to multiple tables.
  • Execute multiple SQL statements.
  • Use temporary tables and variables.
  • Perform conditional logic.
  • Implement complex insert / update workflows.
  • Map one source record to multiple destination tables.

The SQL template must be valid SQL supported by the destination database.

When should I use CUSTOM SQL?

Use CUSTOM when the built-in actions (INSERT, UPDATE, MERGE, …) are not enough. Typical cases:

  • Writing a single source record to multiple destination tables.
  • Populating parent / child relationships.
  • Executing complex, stored-procedure-like logic.
  • Using database-specific SQL features.
  • Operations that require temporary variables, joins, or additional queries.

How does CUSTOM SQL work?

  1. You define the SQL as a template.
  2. Column values from the transformation are referenced using tokens of the form {column_name}.
  3. At runtime, Etlworks converts these tokens into bind variables and supplies values from the dataset.
  4. The template is executed for each record. Batch processing is supported.

How do I configure CUSTOM SQL?

Step 1. Create a flow

Create a flow with at least one transformation whose destination is a database.

Step 2. Configure the transformation

Configure the source-to-destination transformation as usual.

Step 3. Set the Action to CUSTOM and enable Predict Lookup Fields

On the Parameters tab:

  1. Set Action to CUSTOM.
  2. Enable Predict Lookup Fields. This is required for the enhanced CUSTOM mode.

Step 4. Enter the SQL template

In Destination Query, enter the SQL template. Any field referenced as {field_name} automatically becomes a bind variable populated from the transformation dataset.

Example: writing one source record to multiple tables

DECLARE @Inserted TABLE (id INT)
INSERT INTO poc.Property_Value (property_id, value)
OUTPUT INSERTED.id INTO @Inserted(id)
SELECT
   pd.id,
   {value}
FROM poc.Property_Definition pd
WHERE pd.name = {property}

INSERT INTO poc.Key_Value (property_value_id, key_definition_id, key_value)
SELECT
   i.id,
   kd.id,
   v.key_value
FROM @Inserted i
JOIN poc.Key_Group_Definition kg
   ON kg.name = 'config'
JOIN poc.Key_Definition kd
   ON kd.key_group_definition_id = kg.id
JOIN (
   SELECT 'caller'      AS key_name, {caller}      AS key_value
   UNION ALL SELECT 'mandant',        {mandant}
   UNION ALL SELECT 'application',    {application}
   UNION ALL SELECT 'userType',       {userType}
) v
   ON v.key_name = kd.name

How this works

  1. The first statement inserts a record into Property_Value.
  2. The inserted ID is captured into a table variable.
  3. The second statement inserts multiple rows into Key_Value.
  4. Source fields like {value}, {property}, {caller} are converted into bind variables.

A single source record populates multiple relational tables.

Supported tokens

Column values referenced as {column_name} are automatically converted into bind variables. Example:

INSERT INTO table (name, age)
VALUES ({name}, {age})

Using bind variables instead of inline string interpolation:

  • Prevents SQL injection.
  • Improves performance.
  • Enables batch processing.

CUSTOM UPSERT

CUSTOM UPSERT is a special case of CUSTOM SQL for connectors with a native but non-standard UPSERT syntax — for example, the Salesforce connector. Instead of writing the full template with named field tokens, you provide an UPSERT template that uses these positional tokens:

TokenWhat it expands to
{COLUMNS}Comma-separated list of destination columns.
{VALUES}Comma-separated list of bind values for those columns.
{KEYS}Comma-separated list of lookup fields used to identify a unique record.

Example:

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

Use this mode only when the connector requires a specific UPSERT syntax. For everything else — multi-statement SQL, writes to multiple destination tables — use the general CUSTOM SQL mode.

Key notes

  • The SQL template must be valid SQL supported by the destination database.
  • Multiple SQL statements (no comma separation required — depends on the database dialect) are supported.
  • Temporary tables, variables, joins, and complex queries are supported.
  • Batch execution is supported.
  • Predict Lookup Fields must be enabled.

Related articles