Write Table

Operation Name

Write Table

Function Overview

Writes input data to table.

Data Model

Table model type.

Properties

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 the name to show 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 global resource. [Add]: Add a new global resource. [Edit list]: Global resource settings can be edited in “Edit resource list” screen.
Table name	Required	Available	Select or enter a table name to be written to.	Tables of the number specified in the global resource's list settings are displayed. When a schema or table name in the database includes “.”, you can handle the name properly by enclosing it with quotation marks. For example, when the quotation mark is ["] and the schema 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. [Checked]: Table name displayed in [Table name] is enclosed with quotation marks. [Not checked]: (default) Table name displayed in [Table name] is not enclosed with quotation marks. If the table name includes characters like space, you need to enclose the table name with quotation marks. For details on characters that need to be enclosed with quotation marks, inquire the database vendor.	Quotation mark which is set in each database adapter is as follows. Oracle, DB2, PostgreSQL, Amazon RDS for Oracle, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL: [""] (double quotations) Access, SQL Server, Amazon RDS for SQL Server, Azure SQL database: “[]” (box brackets) MySQL, Amazon RDS for MySQL, Amazon Aurora for MySQL: “``” (backquote) There isn't this property in JDBC Adapter. At the time of execution, the table name input in [Table name] is applied as is. 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 at the time of execution. If the table name entered in [Table Name] is not enclosed with quotation marks, this property will be checked at the time of execution, and if this property is enabled, 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 enabling/disabling this property.
Update rows with matching key	Optional	Not available	Select whether to update rows of which the value of the column specified as the key matches or to insert all. [Checked]: Updates rows of which the key value matches. Inserts rows of which the key value does not match. [Not checked]: (default) Inserts all rows.	If [Update rows with matching key] is checked and also [Key] is not specified in [Schema definition], an error occurs. For more details, refer to “Update 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 name for which [Target] or [Key] is checked will be displayed in Mapper schema. Schema definition cannot be displayed in the following cases. When the user do not have read permission to the specified table When the specified table does not exist When a variable is specified in [Table name] When [Table name] is not 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 column. [Checked]: (default) Includes in target [Not Checked]: Does not include in target	The checked column names will be displayed in Mapper schema. Batch operation can be performed by clicking the checkbox displayed in the header. One or more columns should be checked. For more details, refer to “Target Columns”.
Schema definition/Key	Required	Not available	Select keys to be included in update conditions. [Checked]: Include in update conditions. [Not checked]: (default) Doe not include in update conditions.	Enabled when [Update rows with matching key] is checked. The checked column names will be displayed in Mapper schema. If [Update rows with matching key] is checked and also [Key] is not specified in [Schema definition], an error occurs. For more details, refer to “Update Rows with Matching Key”.
Schema definition/Column name	Required	Not available	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 is displayed in “<Native type> (<Internal data type>)” format. Native type: RDBMS specific data type. Internal data type: Data type used internally within DataSpider. Refer to “Internal Data Types” for more details.

Property actions

Item name	Description	Remarks
Launch table browser	Launch the table browser. For details on how to operate, 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 cannot be executed in the following cases. When the specified table does not exist When a variable is specified in [Table name] When [Table name] is not 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 every single line of input data. [Batch insert]: (default) Batch is updated for each row specified in [Batch size].	In JDBC adapter, the default value is [One line insert]. Enabled when [Update rows with matching key] is checked. When there is a type which does not support batch update among the types of columns to be written to, [One line insert] will be executed. For more details on the types which do not support batch update, refer to “Database Adapter Limitations”. Batch update is a function to send data of multiple rows to a database at once and execute a SQL statement.
Update mode	Optional	Not available	Select a writing method for updating data. [Update before Insert]: (default) Execute an UPDATE statement every single row, and if a row with matching key does not exist, execute an INSERT statement. [Insert before update]: Execute an INSERT statement every single row, and if a row with matching key does not exist, execute an INSERT statement. [Batch update before insert]: Batch update an UPDATE statement every multiple rows specified in [Batch size], and batch update an INSERT statement when a row with matching key does not exist.	Enabled when [Update rows with matching key] is checked. 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 mode might not be able to use. For details on update mode which is not available, refer to “Database Adapter Limitations”. When there is a type which does not support batch update among the types of columns to be written to, [Update before insert] will be executed. For more details on the types which do not support batch update, refer to “Database Adapter Limitations”. When [Insert before update] is specified, you have to set unique constraint to the column selected in [Schema definition/Key]. When unique constraint is not set, specify [Update before insert] or [Batch update before insert] and execute. If [Batch update before insert] is specified and the following two conditions are satisfied, then multiple data with duplicate keys might be inserted. There are multiple rows with the same key in the input data. The data with relevant key does not exist in the write table.
Batch size	Optional	Available	Specify the number of SQL statements to batch update.	Enabled when [Batch update before insert] is selected in [Update mode] or [Batch insert] is selected in [Insert mode]. Default value is 1000 and the value will be set to 1000 when you omitted. Allowed values are 1-2147483647.
Enable query timeout	Optional	Not available	Select whether or not to enable query timeout. [Checked]: Set query timeout. [Not checked]: (default) Do not set query timeout.	When you select [Not checked], the behavior of the JDBC driver when query timeout is not set will be applied. There is not this property in the following adapters. Access Adapter
Query timeout (seconds)	Required	Available	Set query timeout.	Enabled when [Enable query timeout] is [Checked]. Allowed values are 0-2147483647. The value input in this property will be specified at an argument for JDBC setQueryTimout() method. Although the seconds to wait execution is generally set for query timeout, the behavior might be different depending on database or using JDBC driver. There is not this property in the following adapters. Access Adapter

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input schema

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

Refer to “Schema of table model type” for more details.

Output schema

None.

Loading schema in Mapper

Schema is loaded automatically.

Refer to “Edit Schema” for more details.

Transaction

Transaction is supported.

PSP Usage

PSP is supported.

Refer to “Parallel Stream Processing” for more details.

Available Component Variables

Component variable name	Description	Remarks
count	The number of written data is stored.	Default value is null.
insert_count	Among the written data, the number of inserted data is stored.	Default value is null.
update_count	Among the written data, the number of updated data is stored.	Default value is null.
error_code	When an error (SQLException) occurs, the corresponding error code is stored.	Default value is null. 0 is stored when finishing normally. Error code is based on each driver specifications.
message_category	When an error occurs, the corresponding message code's category is stored.	Default value is null.
message_code	When an error occurs, the corresponding message code is stored.	Default value is null.
message_level	When an error occurs, the corresponding message code's severity is stored.	Default value is null.
error_type	When an error occurs, the error type is stored.	Default value is null. The error type will be in the following format. Example: “java.io.FileNotFoundException” The contents that will be stored may vary depending on DataSpider Servista versions.
error_message	When an error occurs, the error message is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions.
error_trace	When an error occurs, the error's trace information is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Updating Rows with Matching Key

When updating in Write Table operation, put a check in [Update rows with matching key] and select [Column name] for the key. The row to which the column that has been selected as key matches will be updated and the one that does not match will be inserted. The column name selected as the key will be specified in the WHERE clause of UPDATE statement.

Insert/Update

You can specify multiple keys for 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 checked will be used as the columns to be inserted for INSERT statement.

Values to be inserted in columns that are not 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

[Target] checked Column name

Yes COLUMN1

No COLUMN2

No COLUMN3
Column constraints / settings

COLUMN1 COLUMN2 COLUMN3

No Default value setting exist (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

[Target] checked	Column name
Yes	COLUMN1
No	COLUMN2
No	COLUMN3

COLUMN1	COLUMN2	COLUMN3
No	Default value setting exist (Default value: defaultValue)	Allow NULL

COLUMN1
data

COLUMN1	COLUMN2	COLUMN3
data	defaultValue	null

Behavior on updating

When updating, the column where [Schema definition/Target] is checked will be used as the columns to be updated for UPDATE statement.
Values of the columns that are not included in targets are maintained as such without any update.

Example:

Schema definition

[Target] checked [Key] checked 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

[Target] checked	[Key] checked	Column name
Yes	No	COLUMN1
No	Yes	COLUMN2
No	No	COLUMN3

COLUMN1	COLUMN2	COLUMN3
data1	data2	data3

COLUMN1	COLUMN2
updateValue	data2

COLUMN1	COLUMN2	COLUMN3
updateValue	data2	data3

Difference between “Update before insert” and “Insert before update”

When executing the operation specifying [Update before insert] in [Update mode], an UPDATE statement is executed every single row and an INSERT statement will be executed if not updated.
Therefore, when there are many records to update in input data, execution specifying [Update before insert] is more effective than that specifying [Insert before update].

When executing the operation specifying [Insert before update] in [Update mode], an INSERT statement is executed every single row and an UPDATE statement will be executed if the insertion failed.
Therefore, when there are many records to insert in input data, execution specifying [Insert before update] is more effective than that specifying [Update before insert].
- When not 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.

Error Log

When an error occurs during batch updating, information about the error is output in Dump file as error log.

Output destination

$DATASPIDER_HOME/server/logs/<date directory>/execution/<execution ID directory>/dump.xml

Output contents

Outputs the following information to failureResults element.

key attribute value of result element	Description
dump_error_message	Outputs the dump information of when the error occurred. [SQL]: The SQL statement executed when the error occurred. [Error Records Range]: The starting and ending line numbers where the error was in the input data. [Batch Insert Count]: The number of data succeeded to batch update. [Batch Size]: The batch size. [Error Records]: The data used in batch update when the error occurred. The data of Error Records Range in the input data is output.
error_code	The error code when a SQL Exception occurred.

key attribute value of result element

Description

dump_error_message

Outputs the dump information of when the error occurred.

[SQL]:
The SQL statement executed when the error occurred.
[Error Records Range]:
The starting and ending line numbers where the error was in the input data.
[Batch Insert Count]:
The number of data succeeded to batch update.
[Batch Size]:
The batch size.
[Error Records]:
The data used in batch update when the error occurred. The data of Error Records Range in the input data is output.

error_code

The error code when a SQL Exception occurred.

Depending on database or data type to write to, batch update will not be executed.
For more details , refer to “Write Table” of “Database Adapter Limitations”.
When an error occurs during executing a query, error log is output.
When an error occurs in Write Table operation inside Try operation of Monitor Exception operation, the dump file of that operation is not output.

Specification Limits

When the number of tables is not limited or a large number is specified In the global resource settings, depending on the number of tables in the database, it may take some time to open the properties window.

For more details on the specific limitations of each database adapter, refer to “Database Adapter Limitations”.

Major Exceptions

Exception name	Cause	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify [Input data] or draw data flow.
ResourceNotFoundException Resource definition is not found. Name: []	[Destination] is not specified.	Specify [Destination].
ResourceNotFoundException Resource definition is not found. Name: [<global resource name>]	The resource definition selected in [Destination] is not found.	Check the global resource specified in [Destination].
InvalidPropertyConfigurationException	[Table name] is not set.	Specify [Table name].
InvalidPropertyConfigurationException Target column is not selected.	[Target] of [Schema definition] is not selected.	Select one or more [Target] in [Schema definition].
java.sql.SQLException	A database access error or some other one has occurred.	Check the SQLException message. SQLException message is based on the specifications of the driver. The cause of the error might be output in SQLException message. For more details on messages, contact the database vendor.

Notes

To a schema handled as byte[] type, you can input data of Base64 format or binary format.

For more details on the specific limitations of each database adapter, refer to “Database Adapter Limitations”.