hop-user-manual/modules/ROOT/pages/plugins/transforms/tableoutput.adoc

////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at
  http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////
:documentationPath: /plugins/transforms/
:language: en_US
:page-alternativeEditUrl: https://github.com/apache/incubator-hop/edit/master/plugins/transforms/tableoutput/src/main/doc/tableoutput.adoc
= Table Output

== Description
The Table Output transform allows you to load data into a database table. Table Output is equivalent to the DML operator INSERT. This transform 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.

== Options

[width="90%", options="header"]
|===
|Option|Description
|Transform name|Name of the transform, this name has to be unique in a single pipeline.
|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 Hop 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 a|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:
[source,sql]
----
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.
|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
|===


== Metadata Injection Support

All fields of this transform support metadata injection. You can use this transform with ETL Metadata Injection to pass metadata to your pipeline at runtime.

* **Options**: Target Schema, Target Table, Commit Size, Truncate Table, Specify Database Fields, Partition Data over Table, Use Batch Update for Inserts, Is the Name of Table Defined in a Field?, Store the Tablename Field, and Return Auto-Generated Key
* **Values**: Table and Stream