SQL Insert

Connection

The database connection to use.

Evaluated for each input row

Schema

The database schema of the table.

Evaluated for each input row

Table

The database table to insert into.

Evaluated for each input row

Batch Size

Number of records sent to the database in a batch.

• When set to a number greater than 0, generates batch updates accumulating up to the given amount of records.
• When set to 0 a single batch update is issued after all records are processed.
• When set to nil batch updates are disabled.
• Any other value produces an error.

Evaluated for each output table

Commit Size

Number of records after which data is committed.

• When set to a number greater than 0, commits updates after processing the given amount of records. If batch updates are enabled, commit size must be divisible by batch size.
• When set to 0 a single commit is issued after all records are processed.
• When set to nil this step does not issue any commits. This is useful if you are controlling transactional behaviour manually, or the database does not support transactions.
• Any other value produces an error.

Evaluated for each output table

Primary Key

The name of the primary key field in the database table.

If you wish to retrieve the generated primary key, some databases - Oracle for example - require that you specify the field name explicitly.

Evaluated for each output table

Get Generated Key

If enabled, attempt to get the generated key for each inserted row.

The key is avilable as results.generated_key. When using this feature, batching must be turned off, meaning that batch size must be nil.

Evaluated for each output table

Columns

How table columns are established:

• fixed
  The columns written to the table are static. They are specified directly.

• data driven
  The columns written to the table are established at runtime.

Specified at design time

Table Columns

A list defining the columns written to the table.

Each entry in the list is a dict with the following keys:

• :name - The name of the column
• :type - The data type of the column, one of:
  • any
  • string
  • long
  • double
  • decimal
  • datetime
  • boolean
  • list
  • dict
  • binary

To specify three output columns you would supply a definition such as:

[
  {:name 'id',   :type 'long'},
  {:name 'name', :type 'string'},
  {:name 'sku',  :type 'string'}
]

Evaluated for each output table

Column Values

A list or dict defining the data to write to the table.

If the data structure is a list, the value for each column is extracted in order.

If the data structure is a dict, the values for columns are extracted by using column names as keys.

For example, consider the following column definition:

[
  {:name 'id',   :type 'long'},
  {:name 'name', :type 'string'}
]

We can supply column values as a list:

[123, "Sherlock Holmes"]

We can also supply column values as a dict:

{
  :id 123,
  :name "Sherlock Holmes"
}

Evaluated for each input row

generated_key