(1) Destination DB alias: The destination database alias. Note: Variables (syntax @MYVAR@, type String) can also be used here. These are available via the drop-down menu.

(2) SQL console: See section SQL console.

(3) Creation of SQL statements: Generation of the SQL statement. See following sections. You can use ETL/ELT variables (@VAR@) and ETL/ELT fields ($field).

Mapping (Insert)

(4) Destination table: Selection of destination table.

(5) SQL Auto Commit: With auto commit, the SQL statements are not transaction-based, but executed directly.

(6) Source field: Selection of ETL/ELT source fields, custom ETL/ELT fields, index columns and ETL/ELT variables. See also (7).

(7) Arrow in the middle: Allows you to move selected items (6) and (8) to the right. With the symbol above, entries selected on the left can be assigned to existing entries on the right in order. Drag and drop is also possible.

(8) DB columns: Selection of columns of the selected table (4). See also (7).

(9) Window on the right: Fixed values, ETL/ELT source fields, custom ETL/ELT fields and ETL/ELT variables can be assigned to the columns.

(10) Null values: The replacement value for destination fields without value.

(11) Statement: Here you can view the generated statement.

Mapping (Upsert)

(12) INSERT or UPDATE via WHERE condition: The configuration here is the same as for the insert, but there is an additional field for the WHERE condition. ETL/ELT source fields and custom ETL/ELT fields can be used and ETL/ELT variables.

Custom SQL statement

Here you can specify your own SQL statement.

Note: A semicolon is mandatory for merge statements on MSSQL databases. However, this is removed by default, which leads to an error. To avoid this error, you must change the defined separator in the JDB properties of the alias used (1) from a semicolon to another character, e.g. statement.separator=§