INSERT|V4.4.2|OceanBase Database| docs|Distributed Database

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-compatible 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	Optional. Use the Hint to enable direct load. For more information about the Hint, see Hint overview. Notice During the execution of a direct load task, do not upgrade OceanBase Database. Otherwise, the direct load task may fail. The `INSERT INTO SELECT` statement supports the `APPEND \\| DIRECT()\\| NO_DIRECT` Hint to control the data import method. 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`: The default value, equivalent to using `DIRECT(true, 0)`. It also supports the online statistics collection feature (GATHER_OPTIMIZER_STATISTICS Hint). `DIRECT()`: `bool`: Indicates whether the data to be written needs to be sorted. `true` indicates that sorting is needed. `false` indicates that sorting is not needed. `int`: 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. Indicates full import. `inc`: Indicates incremental import. It supports the `INSERT` and `IGNORE` semantics. `inc_replace`: Indicates incremental import without checking for duplicate primary keys. This is equivalent to incremental import with the `REPLACE` semantics. `enable_parallel_dml parallel(N)`: The data loading parallelism. `N` defaults 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 required. `NO_DIRECT`: Forces a single SQL statement to not use direct load. If the input SQL statement contains this hint, the entire statement ignores other direct load hints and executes a regular import. For more information about using the `INSERT INTO SELECT` statement for direct load, see Use the INSERT INTO SELECT statement for direct load.
table_name	The name of the table to import data to. You can specify any number of columns in the table.
PARTITION_OPTION	The name of the partition for direct load. partition_option_list: A list of partitions to be inserted, separated by commas (,). subpartition_option_list: A list of subpartitions to be inserted, separated by commas (,).
single_table_insert	Single-table insertion.
insert_table_clause	The table to be inserted into, which can be a base table, an updatable view, or a special subquery. Notice A special subquery is similar to an updatable view's subquery. This type of subquery 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 be inserted.
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 insertion.
conditional_insert_clause	Conditional multi-table insertion. `ALL`: Traverses all branches that meet the conditions. As long as the conditions are met, the data is inserted into the corresponding table. `FIRST`: After finding the first branch that meets the conditions, the data is inserted into the corresponding table of this branch, and the conditions of the remaining branches are no longer checked.
error_logging_clause	The SQL errors and the number of affected rows are saved in an error log table.
overwrite_table_insert	Used to replace the existing data in the table or partition with the query results. For more information about the `INSERT OVERWRITE SELECT` statement, see the Use the INSERT OVERWRITE SELECT statement to insert data section in Insert data.
list_of_columns	The columns in the table that need to be inserted with data.
select_stmt	The `SELECT` clause. For more information about the query statement, see SIMPLE SELECT.

Examples

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

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 insertion: Use INSERT ALL. 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 neither condition is 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 insertion: Use INSERT FIRST. 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 neither condition is 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 direct hint and the load_mode parameter.

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 partitioned 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 partitioned direct load.

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

Specify secondary partitioned direct load.

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

OceanBase

Customer Stories

Documentation

INSERT

Purpose

Privilege requirements

Syntax

Parameters

Notice

Note

Notice

Examples

References