INSERT

Purpose

You can use this statement to add one or more records to a table.

Syntax

INSERT [hint_options] { single_table_insert | multi_table_insert }

single_table_insert:
    { INTO insert_table_clause opt_nologging '(' column_list ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    | INTO insert_table_clause opt_nologging '(' ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    | INTO insert_table_clause opt_nologging values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
    }

opt_nologging:
    { NOLOGGING | /*EMPTY*/ }

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

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

Examples

Sample tables and their data are defined as follows:

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

• Single-table insert: Insert a row into the tbl1 table.

  obclient> INSERT INTO tbl1 VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    2 |    3 |
  +------+------+------+
  1 row in set
  

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

  obclient>INSERT INTO (SELECT * FROM tbl1) VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM tbl1;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  1 |    2 |    3 |
  +----+------+------+
  1 row in set
  

• Single-table insert: Insert data into a table, with the RETURNING clause specified in the statement.

  obclient> INSERT INTO tbl1 VALUES(1,2,3) RETURNING col1;
  +------+
  | COL1 |
  +------+
  |    1 |
  +------+
  1 row in set
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    2 |    3 |
  +------+------+------+
  1 row in set
  

• General multi-table insert: Insert one row (1,1,1) into the tbl1 table and one row (2,2,2) into the tbl2 table when the tbl3 table contains at least one row.

  obclient> INSERT ALL INTO tbl1 VALUES(1,1,1)
                                      INTO tbl2 VALUES(2,2,2)
                  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 |    1 |    1 |
  +------+------+------+
  1 row in set
  
  obclient> SELECT * FROM tbl2;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    2 |    2 |    2 |
  +------+------+------+
  1 row in set
  

• Conditional multi-table insert: Use INSERT ALL to insert one row (1,1,1) into the tbl1 table when the value of col2 in the tbl table is greater than 1. Insert one row (2,2,2) into the tbl2 table when the value of col3 in the tbl table is greater than 1. Insert one row (3,3,3) into the tbl1 table when both conditions are not met.

  obclient> INSERT INTO tbl VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient>INSERT ALL
                WHEN col2 > 1 THEN INTO tbl1 VALUES(1,1,1)
                WHEN col3 > 1 THEN INTO tbl2 VALUES(2,2,2)
                ELSE INTO tbl1 VALUES(3,3,3) SELECT col2,col3 FROM tbl;
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  1 |    1 |    1 |
  +----+------+------+
  1 row in set
  
  obclient> SELECT * FROM tbl2;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  2 |    2 |    2 |
  +----+------+------+
  1 row in set
  

• Conditional multi-table insert: Use INSERT FIRST to insert one row (1,1,1) into the tbl1 table and one row (4,4,4) into the tbl2 table when the value of col2 in the tbl table is greater than 1. Insert one row (2,2,2) into the tbl2 table when the value of col3 in the tbl table is greater than 1. Insert one row (3,3,3) into the tbl1 table when both conditions are not met.

  obclient>INSERT INTO tbl VALUES(1,2,3);
  Query OK, 1 row affected
  
  obclient>INSERT ALL
                WHEN col2 > 1 THEN INTO tbl1 VALUES(1,1,1) INTO tbl2 VALUES(4,4,4)
                WHEN col3 > 1 THEN INTO tbl2 VALUES(2,2,2)
                ELSE INTO tbl1 VALUES(3,3,3) SELECT col2,col3 FROM tbl;
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +----+------+------+
  | COL1 | COL2   | COL3   |
  +----+------+------+
  |  1 |    1 |    1 |
  +----+------+------+
  1 row in set
  
  obclient> SELECT * FROM tbl2;
  +------+------+------+
  | COL1   | COL2   | COL3   |
  +------+------+------+
  |    4 |    4 |    4 |
  |    2 |    2 |    2 |
  +------+------+------+
  2 rows in set