Page Operations
Browse SpaceDescription
The Table output step allows you to load data into a database table.
Options
The table below describes the available options for the Table output step:
| Option | Description |
|---|---|
| Step name | Name of the step; this name has to be unique in a single transformation. |
| Connection | The database connection to which data is written |
| Target Schema | The name of the Schema for the table to write data to. This is important for data sources that allow for table names with periods in them. |
| Target table | The name of the table to which data is written. |
| Commit size | Use transactions to insert rows in the database table. Commit the connection every N rows if N is larger than zero (0); otherwise, don't use transactions. (Slower)
|
| Truncate table | Select if you want the table to be truncated before the first row is inserted into the table |
| Ignore insert errors | Makes Kettle ignore all insert errors such as violated primary keys. A maximum of 20 warnings will be logged however. This option is not available for batch inserts. |
| Use batch update for inserts | Enable if you want to use batch inserts. This feature groups inserts statements to limit round trips to the database. This is the fastest option and is enabled by default. |
| Partition data over tables | Use to split the data over multiple tables. For example instead of inserting all data into table SALES, put the data into tables SALES_200510, SALES_200511, SALES_200512, ... Use this on systems that don't have partitioned tables and/or don't allow inserts into UNION ALL views or the master of inherited tables. The view SALES allows you to report on the complete sales:
CREATE OR REPLACE VIEW SALES AS SELECT * FROM SALES_200501 UNION ALL SELECT * FROM SALES_200502 UNION ALL SELECT * FROM SALES_200503 UNION ALL SELECT * FROM SALES_200504 ... |
| Is the name of the table defined in a field. | Use these options to split the data over one or more tables; the name of the target table is defined in the field you specify. For example if you store customer data in the field gender, the data might end up in tables M and F (Male and Female). There is an option to exclude the field containing the tablename from being inserted into the tables. |
| Return auto-generated key | Enable if you want to get back the key that was generated by inserting a row into the table |
| Name of auto-generated key field | Specifies the name of the new field in the output rows that contains the auto-generated key |
| SQL | Generates the SQL to create the output table automatically |
I want an autogenerated key to be added to table while inserting rows.
Field "Return Aoto-generated..." doesn't seem to work
Your table needs to have an autogenerated key column (such as AUTOINCREMENT or an IDENTITY). If your table has this and you enable the option to return that key, then any steps coming after the Table Output step in your transformation will have a new column containing the value that was generated by the DB for each row.
what's the difference between this node with insert/update?