INSERT|V4.3.1| docs|Distributed Database

INSERT

Last Updated：2025-12-04 08:27:57 Updated

Description

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 privilege. For more information about OceanBase Database privileges, see Privilege types in Oracle mode.

Syntax

INSERT [hint_options] { single_table_insert | multi_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 } ]

Parameter explanation

Parameter	Description
hint_options	Specifies hint options. It is optional. For more information about hints, see Overview. The `INSERT INTO SELECT` statement in OceanBase Database's Oracle mode supports enabling direct load by using the `direct` hint. The format is as follows: `/+ direct(bool, int, load_mode)} enable_parallel_dml PARALLEL(N) /`. `bool`: specifies whether the data to be written needs to be sorted. The value `true` indicates that sorting is needed, and the value `false` indicates that sorting is not needed. `int`: the maximum number of error rows tolerated. `load_mode`: the mode of direct load. This parameter is optional. Values must be enclosed in single quotation marks. The available values are as follows: `full`: the default value. It indicates full direct load. `inc_replace`: incremental direct load with the semantics of the `replace` operation. Notice The incremental direct load feature is currently in the experimental stage. It is not recommended to use it in a production environment to avoid stability issues. `enable_parallel_dml parallel(N)`: the degree of parallelism of data loading, with the default value of 4 for `N`. Note Generally, the `enable_parallel_dml` hint and the `parallel` hint must be used together to enable parallel DML. However, if the schema of the target table specifies the table-level parallelism, you only need to specify the `enable_parallel_dml` hint. For more information about direct load by using the `INSERT INTO` statement, see Import data by using the INSERT INTO SELECT statement.
table_name	The name of the destination table. You can specify any number of columns in the destination table.
PARTITION_OPTION	The partition names for partition-wise direct load. The format is as follows: partition_option_list: a comma-separated (,) list of partition names when multiple partitions are to be loaded. subpartition_option_list: a comma-separated (,) list of subpartition names when multiple subpartitions are to be loaded.
single_table_insert	Specifies to insert data into a single destination table.
insert_table_clause	The name of the source table, which can be a base table, updatable view, or special subquery. Notice A special subquery is a subquery similar to an updatable view. It should not contain complex operators (such as `GROUP BY`, `DISTINCT`, and `WINDOW FUNCTION`).
opt_nologging	Specifies to minimize the log information during the insert process.
column_list	The names of the columns to be inserted.
returning_exprs	The projected columns after the data is inserted.
insert_into_clause	The values of the columns after the data is inserted are inserted into the specified columns.
multi_table_insert	Specifies to insert data into multiple destination tables.
conditional_insert_clause	Specifies to insert data into multiple destination tables conditionally. `ALL`: traverse all matching branches and insert data into the corresponding tables as long as the conditions are met. `FIRST`: insert data into the table corresponding to the first matching branch after which the check on the conditions of the remaining branches is stopped.
error_logging_clause	Saves the SQL errors and the values of the affected rows into an error record table.

Examples

The examples below are based on the following definitions:

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 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 rows into the result set of 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: Use 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

Multi-table insert: When the tbl3 table contains at least one row, insert the row (7,8,9) into the tbl1 table and the row (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 the INSERT ALL statement to insert the row (10,11,12) into the tbl1 table when the value in the col2 field of the tbl table is greater than 1; insert the row (22,23,24) into the tbl2 table when the value in the col3 field of the tbl table is greater than 1; if neither condition is met, insert the row (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 the INSERT FIRST statement to insert the row (14,15,16) into the tbl1 table and the row (40,41,42) into the tbl2 table when the value in the col2 field of the tbl table is greater than 1; insert the row (25,26,27) into the tbl2 table when the value in the col3 field of the tbl table is greater than 1; if neither condition is met, insert the row (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

Use the load_mode parameter of the direct hint to specify incremental direct load.

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

References

Previous topic

DELETE

Last

Next topic

MERGE