Automatically create or alter a destination table

Overview

ETL flows can automatically create and alter the destination tables.

Create table

If you enable parameter Create target table if it doesn't exist. Also update columns to match the target, Etlworks will automatically create a destination table if it does not already exist. It uses fields in the source to create and execute CREATE TABLE SQL statements. 

This behavior is enabled by default.

You can find this parameter under Transformation > Mapping > Parameters > Handling schema changes.

Create Indexes when creating table

Additionally, if the source is a database as well, and the source table has indexes, you can enable the parameter Create Indexes. If it is enabled, the Etlworks will create the same indexes in the destination that exists in the source.

Keep order of columns in mapping when creating table

By default when the flow creates a table and there is user-defined mapping it keeps the original order of columns in the source and adds columns which do not exist in the source to the end, therefore completely ignoring the order of columns in the mapping.  For example, if there is mapping like below:

the generated CREATE TABLE SQL will look like below (same order as in the source, new column added to the end):

create table patient_test2 (id  INTEGER ,
   firstName  TEXT ,
   lastName  TEXT ,
   email  TEXT ,
   birthday  TEXT ,
   new_column  TEXT 
)

And the Test transformation will generate the output like below.

In most cases it is good enough (order of columns typically doesn't matter in SQL) but if for any reason you want to keep the order of columns as defined in mapping just enable the Keep order of columns in mapping when creating table.

When it is enabled (using the example above) the generated CREATE TABLE SQL will look like this:

create table patient_test2 (id  INTEGER ,
   new_column  TEXT ,
   lastName  TEXT ,
   firstName  TEXT ,
   email  TEXT ,
   birthday  TEXT 
)

And the Test transformation will generate the output like this:

Create temporary table

If you want to create a temp table enable Create temporary table checkbox. The possible use-case it to load data into temp staging table, the use SQL to populate the actual table.

Alter table 

If you enable parameters Alter target table if the source has columns that the target table doesn't have and Create target table if it doesn't exist. Also update columns to match the target, the system will automatically create and execute ALTER TABLE SQL statement for each column that exists in the source and does not exist in the destination.

This behavior is disabled by default.

You can find this parameter under Transformation > Mapping > Parameters > Handling schema changes.

Programmatically change  auto-generated CREATE and ALTER table SQL

In all flows where the destination is a database Etlworks automatically generates an SQL for creating a new table if it does not exist and [optionally] altering an existing table if the source dataset has changed. 

It automatically translates data types and column names in the source to the corresponding data types and column names in the destination. It also applies overrides defined in the mapping. 

In rare cases, the automatically generated SQL needs to be modified to correct some of the assumptions made by the ETL engine. Read how to override the auto-generated SQL.

Log CREATE and ALTER table DDL statement

If you want to log CREATE and ALTER TABLE DDL statements generated and executed by the flow enable option Log DDL SQL statements.