KNIME Documentation

Create and remove database tables ​

Use DB Table Creator to create a new table in a database, and DB Table Remover to drop an existing one.

Before you begin ​

Create a table with DB Table Creator ​

The DB Table Creator node creates a new database table. Define the structure manually, or derive it dynamically from an input KNIME table.

  1. Connect the DB Connection output port to a DB Table Creator node.
  2. To derive the structure from a KNIME table, connect the table to the second input port.
  3. Click the node once to open its configuration.
  4. In the Settings tab, enter the Schema and Table name, and configure the following options:
    • Create temporary table — creates a table that exists only for the duration of the database session.
    • Fail if table exists — fails with an error if a table with the same name already exists. If disabled, the node succeeds without creating a table when the name is taken.
    • Use dynamic settings — derives column names and types from the input KNIME table. When enabled, the Columns and Keys tabs become read-only previews.
  5. In the Columns tab, define or review the column names and SQL types.
  6. In the Keys tab, define or review primary and unique key columns.
  7. If Use dynamic settings is enabled, configure type and key rules in the Dynamic Columns Settings and Dynamic Keys Settings tabs:
    • Name-Based SQL Type Mapping — maps columns by name pattern (string with wildcards or regular expression) to a SQL type. Takes precedence over type-based mapping.
    • KNIME-Type-Based SQL Type Mapping — maps all columns of a given KNIME type to a SQL type.
  8. Optionally, add SQL to the Additional Options tab to append after the CREATE TABLE statement.
  9. Click Apply and Execute.

TIP

Wildcards supported in name patterns: * matches any number of characters, ? matches exactly one character. For example, KNI* matches all strings starting with KNI.

Remove a table with DB Table Remover ​

The DB Table Remover node drops a table from the database. This is equivalent to executing a SQL DROP statement.

  1. Connect the DB Connection output port to a DB Table Remover node.
  2. Click the node once to open its configuration.
  3. Enter the Schema and Table name, or click Select a table to choose from the Database Metadata Browser.
  4. Configure the following options:
    • Cascade — also removes dependent tables and views that reference the dropped table. Ignored if the database does not support it.
    • Fail if table does not exist — fails if the selected table does not exist. If disabled, the node succeeds even when no table is found.
  5. Click Apply and Execute.

Next steps ​