INSERT|V4.2.5| docs|Distributed Database

INSERT

Last Updated：2026-02-11 07:43:39 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 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 [, projection...]

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 } ]

Parameters

Parameter	Description
hint_options	Specifies the hint options. This is an optional parameter used to optimize the execution plan of a query.
single_table_insert	Specifies a single-table insert. This is used to insert data into a single table.
multi_table_insert	Specifies a multi-table insert. This allows data to be inserted into multiple tables and supports flexible conditional inserts.
insert_table_clause	Specifies the target table for data insertion. This 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. This type of subquery should not contain complex operators such as `GROUP BY`, `DISTINCT`, or `WINDOW FUNCTION`.
opt_nologging	An optional parameter that allows reducing logging during insertion to improve performance. However, this may result in data loss during fault recovery.
column_list	Specifies the list of columns to be inserted. This defines the specific columns on which the insert operation depends.
values_clause	Defines the specific values to be inserted. These can be expressions or default values used to populate the columns.
returning_exprs	Specifies the columns to return after the insert. This allows retrieving information about the newly inserted rows during the insert operation.
insert_into_clause	Specifies the column values to be inserted. This can be specific column names and corresponding value sets.
into_var_list	Specifies the list of variables to receive the insertion results. These can be user-defined variables or referenced names.
conditional_insert_clause	Specifies a conditional multi-table insert. This allows dynamically deciding which target table to insert data into based on set conditions. `ALL`: Traverses all branches that match the conditions and inserts data into the corresponding tables. `FIRST`: Inserts data into the first matching branch and stops checking subsequent conditions.
error_logging_clause	Defines how to handle errors during the insert process and records SQL errors and affected row information into an error logging table. `LOG ERRORS`: Enables error logging. `INTO` : Specifies the name of the error logging table. `(simple_expression)`: An optional expression that specifies detailed information about the error logging. `REJECT LIMIT`: Sets the limit on the number of acceptable errors.
NOLOGGING	Optimizes insert performance by reducing the amount of logging, typically used for large-scale inserts.

Examples

The following example uses the sample tables and data defined in the following section:

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 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: 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: Insert a row (7,8,9) into the tbl1 table and a row (10,11,12) into the tbl2 table when the tbl3 table contains at least one row.

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. If the value of the col2 column in the tbl table is greater than 1, insert data (10,11,12) into the tbl1 table. If the value of the col3 column 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 the INSERT FIRST statement. If the value of the col2 column 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. If the value of the col3 column 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

Previous topic

DELETE

Last

Next topic

MERGE

INSERT

Purpose

Privilege requirements

Syntax

Parameters

Parameters

Notice

Examples