INSERT

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 [INTO] table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt

Parameters

Examples

The following example defines tables and data:

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: 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
  

• General multi-table insert: When the tbl3 table contains at least one row of data, insert one row of data (7,8,9) into the tbl1 table and 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. 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 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 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 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 of 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);
    

References

• Insert data
• Bypass import data by using the INSERT INTO SELECT statement
• SIMPLE SELECT