Added by Matt Casters, last edited by Jens Bleuel on Feb 13, 2014  (view change)

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.

Description

The Table Output step allows you to load data into a database table. Table Output is equivalent to the DML operator INSERT. This step provides configuration options for target table and a lot of housekeeping and/or performance-related options such as Commit Size and Use batch update for inserts.

If you have a database table that has identity columns and you are inserting a record, as part of the insert, the JDBC driver will typically return the auto-generated key it used when performing the insert. Note: This is not supported on all database types.

Performance notes: There are often performance settings specific to a database type that can be set within the database connection JDBC properties. Please see your database manual for specific JDBC performance settings. See also Special database issues and experiences.

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)

Note: Transactions are not supported on all database platforms.

Truncate table Select if you want the table to be truncated before the first row is inserted into the table

Note: Don't use this option when you are running the transformation clustered!

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.
Specify database fields Enable this option to specify the fields in the Database fields tab. Otherwise all fields are taken into account 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
...
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.

Note: There are limiting factors depending on the used database type and further step options. The batch mode is only used when this check box is selected
  1. and the Commit Size is greater than 0
  2. and the Return auto-generated key option is not enabled
  3. and the transformation is not enabled to use unique connections (Transformation settings / Misc / Make the transformation transactional)
  4. and the following rule is false: the database type supports safe points and step error handling is enabled (see the database feature list to check if the database supports safe points)
  5. and the database type supports batch updates (see the database feature list to check this)                         
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.
Field that contains name of table
When the option "Is the name of the table defined in a field?" is enabled, enter the field name to use here.
Store the table name field
When the option "Is the name of the table defined in a field?" is enabled, you can chose is this field should we written to the table or not.
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

what's the difference between this node with insert/update?

Comment: Posted by samuel wu at Mar 19, 2009 01:23

This is an insert. Insert/Update is... And Insert or Update (depending on the defined keys).

Comment: Posted by Tomás Di Doménico at Mar 20, 2009 07:45

I want an autogenerated key to be added to table while inserting rows.

Field "Return Aoto-generated..." doesn't seem to work

Comment: Posted by Darshan Singh at Apr 08, 2009 10:50

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.

Comment: Posted by Daniel Einspanjer at Apr 10, 2009 09:58

Hello,

sorry for my english, just have a look at my username...

I just beginning to to work with GeoKettle 2.0 and i can't insert a shape file into a postgis database. Must i create the structure of the table before the insertion or GeoKettle could create the table and insert the data at the same time?

Thanks

EDIT

Problem solved thanks to the repository subversion and the help of Thierry Badard: http://www.spatialytics.com/forum/#/discussion/22/charger-un-shape-dans-postgis/

Comment: Posted by FrenchClem at Sep 21, 2011 05:09 Updated by FrenchClem

While using this Step i am Inserting a row into an Oracle table mapped to fields from a Text File Input step. I would like to set one date field to SYSDATE (current date/time) and have tried 'Add Constants', 'Set Field Value', 'Set Field Value to a Constant', 'Value Mapper', and several other steps to change the empty/null value coming in from a text file and set it to use SYSDATE somehow so when the Table Output step runs the actual SYSDATE gets inserted into the Date field. Any ideas?

Comment: Posted by Adam Myatt at Jun 28, 2013 15:20

Hi,

How i change the "Insert Statments" in the output table step. i can't insert rows to Postgresql, I think there is a error with the schema but i can't change the insert statements,  i  tested the insert statement in the dbms(Postgresql) and exist a problem whit the query. please help me.

2013/11/13 11:18:23 - Salida Tabla.0 - Prepared statement : INSERT INTO trafico_bss.d_linea (ID_LINEA, PLAN, NOMBRE_CLIENTE, DEPARTAMENTO, AREA) VALUES ( ?,  ?,  ?,  ?,  ?)

2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue: 

2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException: 

2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting row into table [d_linea] with values: [65063400], [CCCrp01K5], [NAKADA KONAMI MARCOS TADASHI], [Santa Cruz], [Santa Cruz de la Sierra, incluye Cotoca, La Guardia y Viru Viru]

2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 

2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row

2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ERROR: column "id_linea" of relation "d_linea" does not exis

2013/11/13 11:18:23 - Salida Tabla.0 - Prepared statement : INSERT INTO trafico_bss.d_linea (ID_LINEA, PLAN, NOMBRE_CLIENTE, DEPARTAMENTO, AREA) VALUES ( ?,  ?,  ?,  ?,  ?)
2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue: 
2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting row into table [d_linea] with values: [65063400], [CCCrp01K5], [NAKADA KONAMI MARCOS TADASHI], [Santa Cruz], [Santa Cruz de la Sierra, incluye Cotoca, La Guardia y Viru Viru]
2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 
2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error inserting/updating row
2013/11/13 11:18:23 - Salida Tabla.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ERROR: column "id_linea" of relation "d_linea" does not exis