Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero 7.2.4
Document Number | TTUTI029
Overview
This document provides guidance on how to use the Tibero Multi Insert feature.
Feature Definition
This is a feature to insert data into multiple tables with a single Insert statement.
- You can insert rows calculated from the subquery into one or more tables.
- There are performance advantages when the amount of data to be inserted is large.
multi_table_insert
| Component | Description |
|---|---|
| ALL into_clause | When using multiple into_clauses after the reserved word ALL, each into_clause is executed once for each result row of the subquery. |
| values_clause | Specifies the column values of the rows to be inserted. Columns from the subquery can be used. The order of the listed column values must be the same as the order of the columns into which values are to be inserted. If columns to insert values into are listed, column values must be listed in the same order; if not listed, column values must be listed according to the column order defined in the table. |
| subquery | Inserts all rows returned by the subquery. When inserting column values via subquery, the number and order of columns returned by the subquery must match the number and order of columns to be inserted. The subquery can reference arbitrary tables or views, and may also reference the tables or views that are the insertion targets. When referencing the target tables or views, they must be referenced by alias. |
| conditional_insert_clause | Determines whether to execute the into_clause based on the WHEN condition. The WHEN condition must reference the select list of the subquery, and a single multi_table_insert can have up to 127 WHEN clauses. |
conditional_insert_clause
| Component | Description |
|---|---|
| ALL | If ALL is specified, all into_clauses whose WHEN conditions are met are executed. If neither ALL nor FIRST is specified, the default is ALL. |
| FIRST | If FIRST is specified, only the first into_clause whose WHEN condition is met is executed, and the rest are skipped. |
| WHEN condition THEN | Specifies the condition in the condition part. If the condition is met, the into_clause is executed. |
| into_clause | Used when you want to insert rows into specific columns only. |
| values_clause | Specifies the column values of the rows to be inserted. The order of the listed column values must be the same as the order of the columns into which values are to be inserted. If columns to insert values into are listed, column values must be listed in the same order; if not listed, column values must be listed according to the column order defined in the table. |
| ELSE | If none of the WHEN conditions are met, the into_clause following the ELSE clause is executed. If there is no ELSE clause, nothing is done. |
Caution
Aliases cannot be assigned to the subquery.
This cannot be performed on views or remote tables; it can only be performed on local tables.
Method
Scenario
Please refer to the attached file (Tibero multi insert scenario).
1. Unconditional INSERT ALL
Executes all multiple INSERT statements.
Caution
When inserting SELECT results into multiple tables, data types must match.
2. Conditional INSERT ALL
Inserts rows that meet the WHEN conditions into multiple tables.
3. Conditional INSERT FIRST
Only inserts for the first WHEN condition that is met are executed.
Note
INSERTs for the remaining conditions are not executed.
Caution
When using sequences, the same sequence value is inserted into the target tables.
Therefore, if the table's primary key uses a sequence, Multi Insert cannot be used.