Official |[RDB] Write Table

Operation name

Write Table

Function overview

Writes input data to a table.

Data model

The data model of this component is table model type.

Tutorials

For details about how to create a connection to a MySQL database, refer to the following tutorial:

Create a connection to a MySQL database

Properties

= Remarks =

For details on use of variables, refer to Variables.

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter a name that is used on the script canvas.
Input data	Required	Not available	Select a component on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Destination	Required	Not available	Select a connection resource. Add A new connection resource can be added. Edit list Connection resource settings can be edited in > HULFT INTEGRATE > Connections.
Table name	Required	Available	Select or enter a table name to be written to.	The number of tables listed is determined in the settings for connection resources. When a schema name or table name in the database includes a period (.), you can ensure the name is handled properly by enclosing the schema name or table name with quotation marks. For example, when the quotation mark is " and the schema name and table name are A.B and C.D respectively, the proper way to enclose is "A.B"."C.D".
Enclose table name with quotation marks	Optional	Not available	Select whether or not to enclose the specified table name with quotation marks. Selected Enclose the table name displayed in Table name with quotation marks. Not selected (Default) Don't enclose the table name displayed in Table name with quotation marks. If the table name includes a character like a space, you need to enclose the table name with quotation marks. For details on characters that need to be enclosed with quotation marks, contact the database vendor.	Quotation marks which is set in each database connector is as follows: Oracle, DB2, PostgreSQL, Amazon RDS for Oracle, Amazon RDS for PostgreSQL: "" (double quotations) SQL Server, Amazon RDS for SQL Server, Azure SQL Database [] (square brackets) MySQL, Amazon RDS for MySQL, Amazon Aurora for MySQL `` (backquotes) Note JDBC Connector doesn't have this property. The table name input in Table name is applied as is during execution. If the table name entered in Table name is enclosed with quotation marks, a query will be issued with the table name enclosed with quotation marks also during execution. If the table name entered in Table name isn't enclosed with quotation marks, this property will be checked during execution, and if this property is selected, a query will be issued with the table name enclosed with quotation marks. When environment variables are used in Table name, the value is deployed when selecting/unselecting this property.
Update rows with matching key	Optional	Not available	Select whether to update rows whose value of the column specified as the key matches or to insert all. Selected Update rows whose key value matches. Insert rows whose key value doesn't match. Not selected (Default) Insert all rows.	If Update rows with matching key is selected and Key isn't specified in Schema definition, an error occurs. For details, refer to Updating rows with matching key.
Schema definition	Required	-	The schema definition of the table specified in Table name is displayed.	Among the columns displayed in Schema definition, the column names for which Target or Key is selected will be displayed in Mapper schema. Schema definition can't be displayed in the following cases: When the user don't have read permission to the specified table When the specified table doesn't exist When a variable is specified in Table name When Table name isn't entered When an error has occurred in Table name
Schema definition/Target	Optional	Not available	Select whether or not to include each column of the table specified in Table name in the target columns to write. Selected (Default) Include in the target to write. Not selected Don't include in the target to write.	The selected column names will be displayed in Mapper schema. You can operate all checkboxes at once by clicking the checkbox displayed in the header. For details, refer to Target columns. Note One or more columns must be selected.
Schema definition/Key	Required	Not available	Select keys to be included as update conditions. Selected Include as update conditions. Not selected (Default) Don't include as update conditions.	Enabled when Update rows with matching key is selected. The selected column names will be displayed in Mapper schema. If Update rows with matching key is selected and Key isn't specified in Schema definition, an error occurs. For details, refer to Updating rows with matching key.
Schema definition/Column name	Required	Not available	A column name in the table specified in Table name is displayed.
Schema definition/Type	Required	Not available	The type of Column name is displayed.	It's displayed in "<Native type> (<Internal data type>)" format. Native type RDBMS specific data type. Internal data type Data type used internally. For details, refer to Internal data types.

Property actions

Item name	Description	Remarks
Launch table browser	Launch the table browser. For details about the operation procedure, refer to Table Browser.	Enabled when Destination is selected. The table specified in Table name is displayed initially.
Update schema definition	Update the schema definition of the table specified in Table name.	It can't be executed in the following cases: When the specified table doesn't exist When a variable is specified in Table name When Table name isn't entered When an error has occurred in Table name

Item name

Description

Remarks

Launch table browser

Launch the table browser.

For details about the operation procedure, refer to Table Browser.

Enabled when Destination is selected.
The table specified in Table name is displayed initially.

Update schema definition

Update the schema definition of the table specified in Table name.

It can't be executed in the following cases:
- When the specified table doesn't exist
- When a variable is specified in Table name
- When Table name isn't entered
- When an error has occurred in Table name

Write settings

Item name	Required/Optional	Use of variables	Description	Remarks
Insert mode	Optional	Not available	Select a writing method for inserting all data. One line insert Execute a SQL statement for every single row of input data. Batch insert (Default) Batch update is performed for the number of rows specified in Batch size.	Not displayed when Update rows with matching key is selected. When a type which doesn't support batch updates is included in the types of the columns to be written to, One line insert is used during execution. For more details on the types which don't support batch updates, refer to Message codes, exception messages, and limitations for each connector. Batch update is a function to send multiple rows of data to a database at once and execute a SQL statement. Note In the JDBC connector, the default value is One line insert.
Update mode	Optional	Not available	Select a writing method for updating data. Update before Insert (Default) Execute an UPDATE statement for every single row, and if a row with a matching key doesn't exist, execute an INSERT statement. Insert before update Execute an INSERT statement for every single row, and if a row with a matching key exists, execute an UPDATE statement. Batch update before insert Execute UPDATE statements in a batch for the number of rows specified in Batch size, and execute INSERT statements in a batch when a row with a matching key doesn't exist.	Displayed when Update rows with matching key is selected. For difference between Update before insert and Insert before update, refer to Difference between "Update before insert" and "Insert before update". Depending on database, some update modes can't be used. For details on update modes which aren't available, refer to Message codes, exception messages, and limitations for each connector. When a type which doesn't support batch updates is included in the types of the columns to be written to, Update before insert is used during execution. For more details on the types which don't support batch updates, refer to Message codes, exception messages, and limitations for each connector. Note When Insert before update is specified, you have to set unique constraint to the column selected in Schema definition/Key. When unique constraint isn't set, specify Update before insert or Batch update before insert for execution. If Batch update before insert is specified and the following two conditions are satisfied, then multiple data records with duplicate keys might be inserted. There are multiple rows with the same key in the input data. The data with relevant key doesn't exist in the write table.
Batch size	Optional	Available	Specify the number of SQL statements to batch update.	Enabled when Batch insert is selected in Insert mode or when Batch update before insert is selected in Update mode. The default value is "1000". If omitted, "1000" will be set. Allowed values are from 1 to 2147483647.
Enable query timeout	Required	Not available	Select whether or not to enable the query timeout. Selected Set the query timeout. Not selected (Default) Don't set the query timeout.	When this field isn't selected, the behavior of the JDBC driver when the query timeout isn't set will be applied.
Query timeout (seconds)	Required	Available	Set the query timeout.	Enabled when Enable query timeout is selected. Allowed values are from 0 to 2147483647. The value input in this property will be specified as an argument for JDBC setQueryTimeout() method. Generally, the seconds to wait for execution is specified for a query timeout, but the behavior might be different depending on the database or the JDBC driver used.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this connector.

Schemas

Input schema

The number of columns varies depending on the Schema definition settings.

= Remarks =

For schema structure, refer to Table model type .

Output schema

None.

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Transaction

Transaction is supported.

Parallel Stream Processing

PSP is supported.

= Remarks =

For details on PSP, refer to Parallel Stream Processing.

Available component variables

Component variable name	Description	Remarks
count	The number of written data records is stored.	The default value is null.
insert_count	The number of inserted records of the written data is stored.	The default value is null.
update_count	The number of updated records of the written data is stored.	The default value is null.
error_code	When an error (SQLException) occurs, the error code corresponding to the error is stored.	The default value is null. 0 is stored when finishing successfully. Note Error codes are based on the specifications of each driver.
message_category	When an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

Updating rows with matching key

When updating in the Write Table operation, select Update rows with matching key and select a column name for the key.

When the column that has been selected as key matches, the row will be updated, and when it doesn't, the row will be inserted. The column name selected as the key will be specified in the WHERE clause of UPDATE statement.

You can specify multiple keys as update conditions. Rows where all the columns selected as key match will be updated.

Target columns

Behavior on insertion

When inserting, the column where Schema definition/Target is selected will be used as the columns to be inserted for INSERT statement.

Note

Values to be inserted in columns that aren't included in targets depend on specifications of database, limitations of column, and the settings.
If no column with NOT NULL constraint is included in the targets, an runtime error will occur.

Example:

Schema definition

Selection for Target	Column name
Yes	COLUMN1
No	COLUMN2
No	COLUMN3

Column constraints/settings

COLUMN1	COLUMN2	COLUMN3
No	Default value is set (Default value: defaultValue)	Allow NULL

Input data

COLUMN1
data

INSERT statement to be executed

INSERT INTO <table name> (COLUMN1) VALUES ('data')

Post-processing data

COLUMN1	COLUMN2	COLUMN3
data	defaultValue	null

Behavior on updating

When updating, the column where Schema definition/Target is selected will be used as the columns to be updated for UPDATE statement.

Values of the columns that aren't included in targets are maintained as such without any update.

Example:

Schema definition

Selection for Target	Selection for Key	Column name
Yes	No	COLUMN1
No	Yes	COLUMN2
No	No	COLUMN3

Pre-processing data

COLUMN1	COLUMN2	COLUMN3
data1	data2	data3

Input data

COLUMN1	COLUMN2
updateValue	data2

UPDATE statement to be executed

UPDATE <table name> SET COLUMN1 = 'updateValue' WHERE COLUMN2 = 'data2'

Post-processing data

COLUMN1	COLUMN2	COLUMN3
updateValue	data2	data3

Difference between "Update before insert" and "Insert before update"

When executing the operation with Update before insert specified in Update mode, an UPDATE statement is executed for every single row and an INSERT statement will be executed if not updated.

Therefore, when there are many records to update in the input data, execution with Update before insert is more effective than execution with Insert before update.
When executing the operation with Insert before update specified in Update mode, an INSERT statement is executed for every single row, and if the insertion failed, an UPDATE statement is executed.

Therefore, when there are many records to insert in the input data, execution with Insert before update is more effective than execution with Update before insert.

= Remarks =
When nothing is updated with the UPDATE statement, the processing succeeds. For instance, the case that an INSERT statement failed due to a cause other than key duplication applies.

Message codes, exception messages, and limitations

Connector	Message code	Exception message	Limitations
Messages and limitations of the DB2 connector
Messages and limitations of the MySQL connector
Messages and limitations of the Oracle connector
Messages and limitations of the PostgreSQL connector
Messages and limitations of the SQL Server connector
Messages and limitations of the JDBC connector
Messages and limitations of the Amazon Aurora for MySQL connector
Messages and limitations of the Amazon RDS for MySQL connector
Messages and limitations of the Amazon RDS for Oracle connector
Messages and limitations of the Amazon RDS for PostgreSQL connector
Messages and limitations of the Amazon RDS for SQL Server connector
Messages and limitations of the Azure SQL Database connector
Messages and limitations of the Snowflake connector