INSERT|V4.3.5| docs|Distributed Database

INSERT

Last Updated：2026-02-03 08:48:37 Updated

Purpose

This statement is used to add one or more records to a table.

Privilege requirements

To execute the INSERT statement, the current user must have the INSERT system privilege. For more information about OceanBase Database privileges, see Privilege classification in Oracle mode.

Syntax

INSERT [hint_options] { single_table_insert | multi_table_insert | overwrite_table_insert};

single_table_insert:
    { INTO insert_table_clause { NOLOGGING | /*EMPTY*/ } '(' column_list ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    | INTO insert_table_clause { NOLOGGING | /*EMPTY*/ } '(' ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    | INTO insert_table_clause { NOLOGGING | /*EMPTY*/ } values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    }

hint_options:
    [/*+ [APPEND | DIRECT(bool, int, load_mode)] enable_parallel_dml PARALLEL(N) | NO_DIRECT */]
    [INTO] table_name [PARTITION(PARTITION_OPTION)]

column_list:
      column_definition_ref [, column_definition_ref...]

returning_exprs:
    projection_col_name [,projection_col_name ...]

insert_into_clause:
    { INTO into_var_list | BULK COLLECT INTO into_var_list}

into_var_list:
    { USER_VARIABLE | ref_name } [, { USER_VARIABLE | ref_name }...]

values_clause:
    VALUES ({ expr | DEFAULT } [, { expr | DEFAULT } ]...  )

returning_exprs:
    projection_col_name [,projection_col_name ...]

multi_table_insert:
    { ALL { insert_into_clause [ values_clause ] [error_logging_clause] }
    | conditional_insert_clause
    } subquery

conditional_insert_clause:
    [ ALL | FIRST ]
    WHEN condition
    THEN insert_into_clause
    [ values_clause ]
    [ error_logging_clause ]
    [ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
    [ WHEN condition
        THEN insert_into_clause
        [ values_clause ]
        [ error_logging_clause ]
        [ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
    ]...
    [ ELSE insert_into_clause
        [ values_clause ]
        [ error_logging_clause ]
        [ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
    ]

error_logging_clause:
    LOG ERRORS [ INTO [schema.] table_name] [ (simple_expression) ] [ REJECT LIMIT { integer | UNLIMITED } ]

overwrite_table_insert:
    [/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt

Parameters

Parameter	Description
hint_options	Specifies the hint to enable direct load. Optional. For more information, see Hints. Notice Do not upgrade OceanBase Database while a direct load task is in progress, because this may cause the direct load task to fail. The `INSERT INTO SELECT` statement supports the `APPEND \\| DIRECT()\\| NO_DIRECT` hint to control the data import mode. The syntax is `/+ [APPEND \\| DIRECT(bool, int, load_mode)] enable_parallel_dml PARALLEL(N) \\| NO_DIRECT /`. The parameters in the hint are described as follows: `APPEND`: By default, it is equivalent to using `DIRECT(true, 0)`. It also enables the online statistics collection feature (GATHER_OPTIMIZER_STATISTICS Hint). `DIRECT()`: `bool`: specifies whether the written data needs to be sorted. `true` indicates that the data needs to be sorted, and `false` indicates that the data does not need to be sorted. `int`: specifies the maximum number of error rows that can be tolerated. `load_mode`: an optional parameter that specifies the direct load mode. The value must be enclosed in single quotation marks. Valid values: `full`: the default value, indicating full import. `inc`: incremental import, which supports the `INSERT` and `IGNORE` semantics. `inc_replace`: incremental import without checking for duplicate primary keys, equivalent to incremental import with the `REPLACE` semantics. `enable_parallel_dml parallel(N)`: the parallelism of data loading, with `N` defaulting to 4. Note Generally, the `enable_parallel_dml` and `parallel` hints must be used together to enable parallel DML. However, if the target table's schema specifies a table-level parallelism, only the `enable_parallel_dml` hint is needed. `NO_DIRECT`: forces a single SQL statement to not use direct load. If the SQL statement contains this hint, it ignores other direct load hints and performs a regular import. .
table_name	The name of the table to import data into. You can specify any number of columns.
PARTITION_OPTION	Specifies the partition name for direct load. The value is a list of partitions separated by commas (,). partition_option_list: a list of partitions. subpartition_option_list: a list of subpartitions.
single_table_insert	Single-table insertion.
insert_table_clause	The table to insert data into, which can be a base table, an updatable view, or a special subquery. Notice A special subquery is similar to the subquery corresponding to an updatable view. Such subqueries should not contain complex operators (such as `GROUP BY`, `DISTINCT`, or `WINDOW FUNCTION`).
opt_nologging	Minimizes the amount of log information generated during insertion.
column_list	The names of the columns to insert.
returning_exprs	The projected columns after the data is inserted.
insert_into_clause	The column values after the data is inserted are inserted into the specified list.
multi_table_insert	Multi-table insert.
conditional_insert_clause	Conditional multi-table insert. `ALL`: Traverses all branches that meet the conditions, and inserts data into the corresponding tables as long as the conditions are met. `FIRST`: Finds the first branch that meets the condition, inserts data into the corresponding table of the branch, and does not continue to check whether the conditions of the remaining branches are met.
error_logging_clause	The values of SQL errors and the number of affected rows are saved in an error record table.
overwrite_table_insert	Used to replace the existing data in the table or partition with the query result. For more information about the `INSERT OVERWRITE SELECT` statement, see the Insert data by using the INSERT OVERWRITE SELECT statement section in Insert data.
list_of_columns	The columns in the table that need to be inserted.
select_stmt	The `SELECT` clause. For more information about query statements, see SIMPLE SELECT.

Examples

The following example table and data are defined as follows:

obclient> CREATE TABLE tbl(col1 INT PRIMARY KEY, col2 INT,col3 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 INT,col3 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE tbl3(col1 INT PRIMARY KEY, col2 INT,col3 INT);
Query OK, 0 rows affected

Single-table insert: Insert two rows of data into the tbl1 table.

obclient> INSERT INTO tbl1 (col1, col2, col3) VALUES (1, 10, 100), (2, 20, 200);
Query OK, 2 row affected

obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
+------+------+------+
2 rows in set

Single-table insert: Insert data directly into a subquery.

obclient>INSERT INTO tbl1 (col1, col2, col3) SELECT col1 + 2, col2 + 5, col3 + 10 FROM tbl1;
Query OK, 2 row affected

obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   15 |  110 |
|    4 |   25 |  210 |
+------+------+------+
4 rows in set

Single-table insert: Contains the RETURNING clause.

obclient> INSERT INTO tbl1 (col1, col2, col3) VALUES (5, 50, 500), (6, 60, 600) RETURNING col1;
+------+
| COL1 |
+------+
|    5 |
|    6 |
+------+
2 rows in set

obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   15 |  110 |
|    4 |   25 |  210 |
|    5 |   50 |  500 |
|    6 |   60 |  600 |
+------+------+------+
6 rows in set

Normal multi-table insert: When the tbl3 table has at least one row of data, insert one row of data (7,8,9) into the tbl1 table, and insert one row of data (10,11,12) into the tbl2 table.

obclient> INSERT ALL INTO tbl1 VALUES(7,8,9)
                                    INTO tbl2 VALUES(10,11,12)
                SELECT * FROM tbl3 WHERE ROWNUM< 2;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   15 |  110 |
|    4 |   25 |  210 |
|    5 |   50 |  500 |
|    6 |   60 |  600 |
|    7 |    8 |    9 |
+------+------+------+
7 rows in set

obclient> SELECT * FROM tbl2;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|   10 |   11 |   12 |
+------+------+------+
1 row in set

Conditional multi-table insert: Use INSERT ALL. When the value of col2 in the tbl table is greater than 1, insert data (10,11,12) into the tbl1 table; when the value of col3 in the tbl table is greater than 1, insert data (22,23,24) into the tbl2 table; if none of the conditions are met, insert data (33,34,35) into the tbl1 table.

obclient> INSERT INTO tbl VALUES(1,2,3);
Query OK, 1 row affected

obclient>INSERT ALL
              WHEN col2 > 1 THEN INTO tbl1 VALUES(10,11,12)
              WHEN col3 > 1 THEN INTO tbl2 VALUES(22,23,24)
              ELSE INTO tbl1 VALUES(33,34,35) SELECT col2,col3 FROM tbl;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   15 |  110 |
|    4 |   25 |  210 |
|    5 |   50 |  500 |
|    6 |   60 |  600 |
|    7 |    8 |    9 |
|   10 |   11 |   12 |
+------+------+------+
8 rows in set

obclient> SELECT * FROM tbl2;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|   10 |   11 |   12 |
|   22 |   23 |   24 |
+------+------+------+
2 rows in set

Conditional multi-table insert: Use INSERT FIRST. When the value of col2 in the tbl table is greater than 1, insert data (14,15,16) into the tbl1 table and data (40,41,42) into the tbl2 table; when the value of col3 in the tbl table is greater than 1, insert data (25,26,27) into the tbl2 table; if none of the conditions are met, insert data (35,36,37) into the tbl1 table.

obclient>INSERT ALL
              WHEN col2 > 1 THEN INTO tbl1 VALUES(14,15,16) INTO tbl2 VALUES(40,41,42)
              WHEN col3 > 1 THEN INTO tbl2 VALUES(25,26,27)
              ELSE INTO tbl1 VALUES(35,36,37) SELECT col2,col3 FROM tbl;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   15 |  110 |
|    4 |   25 |  210 |
|    5 |   50 |  500 |
|    6 |   60 |  600 |
|    7 |    8 |    9 |
|   10 |   11 |   12 |
|   14 |   15 |   16 |
+------+------+------+
9 rows in set

obclient> SELECT * FROM tbl2;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|   10 |   11 |   12 |
|   22 |   23 |   24 |
|   25 |   26 |   27 |
|   40 |   41 |   42 |
+------+------+------+
4 rows in set

Specify incremental direct load by using the load_mode parameter of the direct hint.

obclient [SYS]> INSERT /*+ direct(true, 0, 'inc_replace') enable_parallel_dml parallel(2) */ INTO tbl2 SELECT * FROM tbl1;

The return result is as follows:

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

Specify partition direct load. Assume that the partitioning rules for the tbl1 and tbl2 tables are as follows:

partition p0 values less than (10)
    subpartition p0_1 values less than (5)
    subpartition p0_2 values less than (10)
partition p1 values less than (20)
    subpartition p1_1 values less than (15)
    subpartition p1_2 values less than (20)

Specify primary partition direct load

insert /*+ enable_parallel_dml parallel(3) append */
into tbl2 partition(p0, p1)
select * from tbl1 partition(p0, p1);

Specify secondary partition direct load

insert /*+ enable_parallel_dml parallel(3) append */
into tbl2 partition(p0sp0_1, p1sp1_1)
select * from tbl1 partition(p0sp0_1, p1sp1_1);

References

Insert data
Use the INSERT INTO SELECT statement to perform direct load
SIMPLE SELECT