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?
- You define the SQL as a template.
- Column values from the transformation are referenced using tokens of the form {column_name}.
- At runtime, Etlworks converts these tokens into bind variables and supplies values from the dataset.
- 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:
- Set Action to CUSTOM.
- 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
- The first statement inserts a record into Property_Value.
- The inserted ID is captured into a table variable.
- The second statement inserts multiple rows into Key_Value.
- 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:
| Token | What 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.