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

Notice

A special subquery is similar to a subquery that defines an updatable view. These subqueries must not contain complex operators (such as GROUP BY, DISTINCT, and WINDOW FUNCTION).

Examples

Execute the following statements to prepare tables and data used in the examples.

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