KNIME Documentation

Insert, update, or merge data ​

Use the DB Writer, DB Row Inserter, DB Updater, or DB Merger node to write data from a KNIME table into a database table.

Before you begin ​

Write and auto-create a table with DB Writer ​

The DB Writer node inserts rows from a KNIME table into a database table and creates the table automatically if it does not exist.

  1. Connect the DB Connection output port to a DB Writer node, and connect the KNIME data table to the second input port.
  2. Click the node once to open its configuration.
  3. Under Database Table, enter the Schema name and Table name, or click Browse to select an existing table.
  4. Under If a database error occurs, choose Fail to stop on the first error, or Ignore to skip failed rows and continue.
  5. Set Batch size to the number of rows to send per database statement.
  6. Under If table exists in database, choose Append rows, Remove existing table and contained data, or Fail.
  7. Under Changing, select the columns to write in Columns to write (SET in SQL).
  8. Under Output, tick Append status column or Append error column to add the corresponding column to the KNIME output table, and set their column names.
  9. Click Show advanced settings to configure Disable DB Data output port or Output Type Mapping. See Type mapping.
  10. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

TIP

Use DB Table Creator when you need precise control over column types, primary keys, or other table properties before writing.

Insert rows into an existing table with DB Row Inserter ​

The DB Row Inserter node inserts rows from a KNIME table into an existing database table. The target table must already exist and column names must match exactly.

  1. Connect the DB Connection output port to a DB Row Inserter node, and connect the KNIME data table to the second input port.
  2. Click the node once to open its configuration.
  3. Under Database Table, enter the Schema name and Table name of the target table, or click Browse to select it.
  4. Under If a database error occurs, choose Fail or Ignore.
  5. Set Batch size to the number of rows to send per database statement.
  6. Under Changing, select the columns to write in Columns to write (SET in SQL).
  7. Under Output, tick Append status column or Append error column to add the corresponding column to the KNIME output table, and set their column names.
  8. Click Show advanced settings to configure Disable DB Data output port or Output Type Mapping.
  9. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

Update existing rows with DB Updater ​

The DB Updater node updates rows in a database table using values from a KNIME table. Use identification columns in the WHERE clause to match rows, and update columns in the SET clause to supply new values.

  1. Connect the DB Connection output port to a DB Updater node, and connect the KNIME data table to the second input port.
  2. Click the node once to open its configuration.
  3. Under Database Table, enter the Schema name and Table name, or click Browse to select the target table.
  4. Under If a database error occurs, choose Fail or Ignore.
  5. Set Batch size to the number of rows to send per database statement.
  6. Under Changing, select the Columns for row identification (WHERE in SQL) and the Columns to write (SET in SQL).
  7. Under Output, tick Append status column or Append error column to add the corresponding column to the KNIME output table, and set their column names.
  8. Click Show advanced settings to configure Disable DB Data output port or Output Type Mapping.
  9. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

Insert new rows and update existing ones with DB Merger ​

The DB Merger node combines the behavior of DB Row Inserter and DB Updater. It issues a MERGE statement if the database supports it. If not, it first attempts an update, then inserts any rows that had no match.

  1. Connect the DB Connection output port to a DB Merger node, and connect the KNIME data table to the second input port.
  2. Click the node once to open its configuration.
  3. Under Database Table, enter the Schema name and Table name, or click Browse to select the target table.
  4. Under If a database error occurs, choose Fail or Ignore.
  5. Set Batch size to the number of rows to send per database statement.
  6. Under Changing, select the Columns for row identification (WHERE in SQL) and the Columns to write (SET in SQL).
  7. Under Output, tick Append status column or Append error column to add the corresponding column to the KNIME output table, and set their column names.
  8. Click Show advanced settings to configure Disable DB Data output port or Output Type Mapping.
  9. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

Next steps ​