Purpose
This statement is used to add one or more records to a table.
Limitations and considerations
You cannot directly perform an INSERT operation on a subquery, for example, INSERT INTO (SELECT * FROM t1) VALUES(1, 1).
Privilege requirements
- You must have the
INSERTprivilege on the target table to execute theINSERTstatement. - To execute the
INSERT ... SELECT ...statement, you must have not only theINSERTprivilege on the target table but also theSELECTprivilege on the source table (the table referenced in theSELECTclause).
For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
INSERT {into_table_insert | overwrite_table_insert};
into_table_insert:
[hint_options] [IGNORE] [INTO]
single_table_insert
[ON DUPLICATE KEY UPDATE update_asgn_list]
hint_options:
[/*+ [APPEND | DIRECT(bool, int, ['load_mode'])] enable_parallel_dml PARALLEL(N) | NO_DIRECT */]
load_mode:
full
| inc
| inc_replace
single_table_insert:
table_name [PARTITION (partition_name, ...)] [(column_list)] {VALUES | VALUE} (values_list)[, (values_list) ...]
| table_name [PARTITION (partition_name, ...)] [(column_list)] select_stmt
| table_name [PARTITION (partition_name, ...)] SET update_asgn_list
column_list:
column_name [,column_name ...]
values_list:
value [,value ...]
value:
expr
| DEFAULT
update_asgn_list:
column_name = expr [,column_name = expr ...]
overwrite_table_insert:
[/*+ PARALLEL(N)*/] OVERWRITE [INTO] table_name [(column_list)] [PARTITION (partition_name, ...)] select_stmt;
Parameters
Parameters |
Description |
|---|---|
| into_table_insert | Inserts data into a table. |
| hint_options | Optional. Specifies hint options. For more information, see hint_options. |
| IGNORE | Optional. Specifies that errors that occur during data insertion will be ignored. |
| INTO | Optional. Indicates the following is the target to insert. This option can be omitted. |
| single_table_insert | Specifies how data is inserted into a table. Currently, three methods are supported for inserting data. For more information, see single_table_insert. |
| table_name | Specifies the name of the table into which to insert data. |
| PARTITION (partition_name, ...) | Optional. Specifies into which partition of the table the data will be inserted. Separate multiple partitions with commas (,). partition_name indicates that you want to insert data into the specified partition of a table. |
| column_list | Optional. Specifies the columns into which to insert data. When inserting multiple columns, separate them with commas (,). |
| column_name | Represents the column name to be inserted into a specified column of a table. |
| ON DUPLICATE KEY UPDATE update_asgn_list | Optional. Specifies the operation to perform when a duplicate primary key or unique key is found.
update_asgn_list Represents an assignment statement. For more information, see update_asgn_list. |
| overwrite_table_insert | This statement is used to replace the existing data in a table or partition with query results. For more information about using the INSERT OVERWRITE SELECT statement, see Insert data in the Insert data by using the INSERT OVERWRITE SELECT statement section. |
| select_stmt | Specify SELECT clause. For more information about the query statement, see SELECT. |
hint_options
[APPEND | DIRECT(bool, int, [load_mode])] enable_parallel_dml PARALLEL(N):APPEND | DIRECT(bool, int, [load_mode]): Optional. Use this hint to enable direct load.Notice
We recommend that you do not upgrade the OceanBase AI Database while a direct load task is in progress, as this may cause the task to fail.
APPEND: The default value. Equivalent to usingDIRECT(true, 0). It also enables online statistics collection (GATHER_OPTIMIZER_STATISTICShint).DIRECT(bool, int, [load_mode]):bool: Indicates whether the written data needs to be sorted. Valid values:true: Indicates that sorting is required.false: indicates that sorting is not required.
int: The maximum number of erroneous rows that can be tolerated.load_mode: Optional. Specifies the direct load mode. The value must be enclosed in English single quotes and can be one of the following:full: The default value, indicating a full import.inc: used for incremental direct load scenarios. In case of a primary key conflict, the data is either inserted or the conflict is ignored. The specific behavior depends on options such asIGNOREin the statement and the execution semantics inincmode. For limitations on incremental direct load, see Data import overview.inc_replace: Indicates an incremental import, but does not check for duplicate primary keys. This is equivalent to an incremental import with theREPLACEsemantics.Notice
When
load_modeis set toinc_replace, theINSERTstatement cannot contain theIGNOREkeyword.
For more information about direct load using the
INSERT INTO SELECTstatement, see Data import overview.
enable_parallel_dml parallel(N): Optional. The degree of parallelism for data loading.Note
In general, the
enable_parallel_dmlhint and theparallelhint must be used together to enable parallel DML. However, if a table-level parallelism is specified for the target table's schema, only theenable_parallel_dmlhint is required.
NO_DIRECT: forces a single SQL statement not to use direct load. As long as an input SQL statement contains this hint, it will ignore other direct load hints and execute as a normal import.
For more information about hints, see Optimizer hint.
single_table_insert
[(column_list)] {VALUES | VALUE} (values_list)[, (values_list) ...]: directly inserts a set of specific values into the target table.column_list: Optional. Specifies the list of column names corresponding to the data to be inserted. Separate multiple column names with commas (,).VALUES | VALUE: A keyword used to introduce the list of values to be inserted.values_list: Specifies the list of data values to be inserted, enclosed in parentheses (()). Multiple value lists are allowed, separated by commas (,), with each value list representing a row of data.values: The single values to be inserted, one value per column, separated by commas (,). You can use an expression (expr) or theDEFAULTkeyword to use the default value of a column.
Example:
Insert a row of data into table
tbl1.INSERT INTO tbl1(col1, col2) VALUES(1, 'A1');Insert multiple rows of data into table
tbl1.INSERT INTO tbl1(col1, col2) VALUES(2, 'A2'),(3, 'A3');
[(column_list)] select_stmt: Selects data from one or more source tables and inserts the query results into a target table.column_list: Optional. Specifies the list of column names corresponding to the inserted data. Separate multiple column names with commas (,).select_stmt: specifies the query statement to be executed, and its results will be inserted into the target table. Theselect_stmtmust return a result set. For details about the structure and options of the query statement, see SELECT.
Example:
INSERT INTO tbl1(col1, col2) SELECT col1, col2 FROM tbl2 WHERE col2 = 'a3';SET update_asgn_list: Specifies the values for each column using theSETclause to insert data into the target table.update_asgn_listrepresents an assignment statement. For more details, see update_asgn_list below.Example:
INSERT INTO tbl1 SET col1= 4, col2='a4';
update_asgn_list
column_name = expr [, ...]: The column name equals the list of expressions. This is used to update existing rows. Multiple value pairs are separated by commas (,).
Example:
Specifies that when a key conflict occurs, the value of column
col2in the target row is updated to the value that was originally attempted to be inserted (i.e.,VALUES(col2), which isa2in this example).INSERT INTO tbl1(col1, col2) VALUES (2, 'a2') ON DUPLICATE KEY UPDATE col2 = VALUES(col2);
Examples
The sample tables test_tbl1 and test_tbl2 are defined as follows:
CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT)
PARTITION BY KEY(col1)
(PARTITION p0,
PARTITION p1,
PARTITION p2,
PARTITION p3
);
CREATE TABLE test_tbl2(col1 INT PRIMARY KEY, col2 INT);
Insert a single row of data into partition
p0of tabletest_tbl1.INSERT INTO test_tbl1 PARTITION(p0) (col1) VALUES(5);View the data in table
test_tbl1:SELECT * FROM test_tbl1 PARTITION(p0);The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 5 | NULL | +------+------+ 1 row in setInsert values into updatable view
v.CREATE VIEW v AS SELECT * FROM test_tbl2;INSERT INTO v VALUES (1, 1);Query data from view
v:SELECT * FROM v;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 1 | +------+------+ 1 row in setExample of
INSERT OVERWRITE SELECT:Insert data into the
test_tbl1table.INSERT INTO test_tbl1 VALUES (1, 30),(2, 25),(3, 22);Use the
INSERT OVERWRITE SELECTstatement to filter data fromtest_tbl1wherecol2is greater than 25, and insert this data intotest_tbl2, replacing its existing content.INSERT OVERWRITE test_tbl2 SELECT * FROM test_tbl1 WHERE col2 > 25;View the data in the
test_tbl2table after the data replacement.SELECT * FROM test_tbl2;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 30 | +------+------+ 1 row in set
Limitations on direct load (INSERT syntax)
When using INSERT INTO ... SELECT ... with hints such as APPEND, DIRECT(...}, enable_parallel_dml, and parallel(N) to perform direct load, in addition to the scope of application and limitations of the direct load capability described in Data import overview, the following statement-level restrictions for INSERT INTO SELECT also apply:
Full Direct Load
- Only Parallel Data Manipulation Language (PDML) is supported for direct load. For more information about parallel DML, see Parallel DML.
- Direct load is a DDL statement and cannot be executed in a multi-row transaction (a transaction containing multiple operations).
- Cannot be executed within a
BEGINblock. Autocommitmust be set to 1.
Incremental Direct Load
- Only PDML is supported; non-PDML operations cannot use direct load. For more information about parallel DML, see Parallel DML.
- Tables with indexes (excluding primary keys) do not support incremental direct load.
Note
Trigger, generated columns, Liboblog, flashback queries, and the limitations of incremental direct load on the target table's structure (foreign keys, GLOBAL indexes, constraints, unique indexes, etc.) are general restrictions related to the direct load capability. For more information, see Overview of data import - Considerations for full/incremental direct load.