SQL Transformations

This section contains all modules that use SQL to formulate transformations.

These modules can only be used within a database, i.e. sources and targets must be in the same database.

• Insert Module
• Update Module
• Upsert Module
• Check Module
• History Module

Notes

There is an option to set "Skip if empty" or "Error if empty".

When this is done, the action is first executed once to see if anything is returned. An attempt is made to support the optimizer of the respective database so that not everything needs to be calculated.

As always, there are a few outliers that have their own syntax for reducing the result set.

For SQL Server and Teradata, the query is embedded in:

SELECT TOP 1 1 FROM (<query>) a

For Oracle:

SELECT 1 FROM (<query>) a WHERE rownum = 1

For all other databases:

SELECT 1 FROM (<query>) a WHERE LIMIT 1

Not all available databases have been tested. After research, no other outlier was found.

The following should know the LIMIT clause (all with (G) have been verified):

• BIGQUERY
• DB2
• DERBY
• EXASOL (G)
• H2 (G)
• HSQL
• MARIADB
• MYSQL (G)
• POSTGRES (G)
• REDSHIFT
• SNOWFLAKE
• SQLITE