SQL Batch Executor
Last updated
Last updated
This component is used to execute Create/Update/Delete operations for multiple input records to the target table. It can execute only one or two queries unlike SQL Executor.
The available operations of this component are these.
Insert
Update
Delete
Insert & Skip - If insert fails with duplicate error, ignore the error.
Update & Skip - If update does not affect any record, ignore that zero update.
Insert & Update - If insert fails with duplicate error, update is executed.
Update & Insert - If update does not affect any record, insert is executed.
This component can process the records concurrently with ParallelCount property. If ParallelCount is 1, this is single thread execution. If the count is greater than 1, this is parallel execution with multiple threads.
The query used for Insert & Skip to Mysql and Postgresql are not same as other databases.
· The INSERT IGNORE syntax in MySQL allows you to insert data into a table without causing an error if a duplicate key exists. Instead of throwing an error, the statement simply skips the insertion of the row and returns a warning. The update count is set to 0 because no row was actually inserted into the table. This can be useful when you want to insert data into a table but don't want to have to check for duplicates beforehand.
· Postgresql has ON CONFLICT(primary_key_name) DO NOTHING syntax. This does same thing as IGNORE of Mysql.
Most databases allow to commit partial records except the failed records, but Postgresql rolls back entire records if any record fails in a transaction. Postgresql has a feature called Atomicity, which ensures that either all operations of a transaction succeed or none of them do. If any part of a transaction fails, the entire transaction is rolled back and all changes made during the transaction are undone. This means that partial records cannot be committed in Postgresql, and the database will always maintain data consistency.
INSERT IGNORE is used to avoid unnecessary check for duplicates but ON CONFLICT of Postgresql is inevitable for Insert & Skip operation.
The Mapping component is used to transform the input data to match the columns of the target table. The output of the Mapping component is then passed to the SQLBatch component for bulk insertion into the target table.
Like SQL Executor component, this component also has the extended properties for the custom database types.
This component does not generate result set like SQL Executor. No select operation is available.
This example is simple DB - DB synchronization.
This flow has two routing paths(__RoutingPath). One is single and the other is parallel. These paths are about ParallelCount. SQLExecutor component retrieves data from the source table, mapping component generates input data for SQLBatchExecutor, and SQLBatchExecutor inserts the records to the target table.
These results are about 300k records which have 400+ bytes respectively.
ParallelCount = 1
ParallelCount = 10
Source database
Target database
| Attribute | Description |
|---|---|
| Attribute | Description |
|---|---|
System Id
Database system id
Data Structure Id
Data structure id for table layout. This id is used in mapping.
Table Name
Target table
Timeout
Query timeout. Query timeout is waiting time until one query execution is complete. This is about one execution of single record or batch records.
Query Type
Literal - query is constructed as a literal.
Prepared - query is constructed with ?
Operation Type
SQL Query
CRUD Type
Target operation
Mapping Info
Input record for the operation
Input Media
Input source
File
Parameter
Input Count
Input record count.
This property specifies the number of input records that the component should read before completing its execution.
If the input data has more records than the specified InputCount, the component will stop reading records after it has reached the specified count. However, if the input data has fewer records than the specified InputCount, the component will stop reading records after it has reached the end of the data.
In summary, the InputCount property determines the maximum number of records that the component will read and execute.
If empty, the entire input records will be read and executed.
Query
User defined query
Parallel Count
Worker count. Default is 1
ResultCode
Error code from the database
ResultMessage
Error message from the database
ResultCount
Insert/Update/Delete count
