INSERT

Purpose

This statement is used to add one or more records to a table.

Limitations and considerations

You cannot directly insert data into a subquery. For example, you cannot execute the INSERT INTO (SELECT * FROM t1) VALUES(1, 1) statement.

Privilege requirements

• To execute the INSERT statement, you must have the INSERT privilege on the target table.
• To execute the INSERT ... SELECT ... statement, you must have the INSERT privilege on the target table and the SELECT privilege on the source table (the table specified in the SELECT clause).

For more information about the privileges of OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

INSERT {into_table_insert | overwrite_table_insert};

into_table_insert:
    [hint_options] [IGNORE] [INTO]
    single_table_insert
    [ON DUPLICATE KEY UPDATE update_asgn_list]

hint_options:
    [/*+ [APPEND | DIRECT(bool, int, ['load_mode'])] enable_parallel_dml PARALLEL(N) | NO_DIRECT */]

load_mode:
    full
    | inc
    | inc_replace

single_table_insert:
    table_name [PARTITION (partition_name, ...)] [(column_list)] {VALUES | VALUE} (values_list)[, (values_list) ...]
    | table_name [PARTITION (partition_name, ...)] [(column_list)] select_stmt
    | table_name [PARTITION (partition_name, ...)] SET update_asgn_list

column_list:
    column_name [,column_name ...]

values_list:
    value [,value ...]

value:
    expr
    | DEFAULT

update_asgn_list:
    column_name = expr [,column_name = expr ...]

overwrite_table_insert:
    [/*+ PARALLEL(N)*/] OVERWRITE [INTO] table_name [(column_list)] [PARTITION (partition_name, ...)] select_stmt;

Parameters

hint_options

• [APPEND | DIRECT(bool, int, [load_mode])] enable_parallel_dml PARALLEL(N):

  • APPEND | DIRECT(bool, int, [load_mode]): optional. Enables direct load by using a hint.

    Notice

    During the execution of a direct load task, we recommend that you do not upgrade OceanBase Database, because this may cause the direct load task to fail.

    • APPEND: the default value, which is equivalent to using DIRECT(true, 0). It also enables the online statistics collection feature (GATHER_OPTIMIZER_STATISTICS Hint).

    • DIRECT(bool, int, [load_mode]):

      • bool: specifies whether the data to be written needs to be sorted. Valid values: true and false.

        • true: indicates that the data needs to be sorted.
        • false: indicates that the data does not need to be sorted.

      • int: specifies the maximum number of error rows that can be tolerated.

      • load_mode: optional. Specifies the direct load mode. Valid values: full, inc, and inc_replace. The value must be enclosed in single quotation marks (').

        • full: the default value, which 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. It is equivalent to incremental import with the REPLACE semantics.

          Notice

          When load_mode is set to inc_replace, the IGNORE keyword is not allowed in INSERT statements.

        For more information about the INSERT INTO SELECT statement, see Overview of direct load.

  • enable_parallel_dml parallel(N): optional. Specifies the degree of parallelism for data loading.

    Note

    Generally, you must use both the enable_parallel_dml and parallel hints to enable parallel DML. However, if a table-level parallelism hint is specified in the schema of the target table, you only need to specify the enable_parallel_dml hint.

• NO_DIRECT: specifies that a single SQL statement must not use direct load. If this hint is specified in the input SQL statement, the statement ignores other direct load hints and executes normal import.

For more information about hints, see Optimizer Hint.

single_table_insert

• [(column_list)] {VALUES | VALUE} (values_list)[, (values_list) ...]: inserts a set of specified values into the target table.

  • column_list: optional. Specifies the list of column names to which the data is inserted. Separate multiple column names with commas (,).

  • VALUES | VALUE: specifies the keyword to introduce the value list to be inserted.

  • values_list: specifies the data value list to be inserted. Enclosed in parentheses (()). You can specify multiple value lists. Separate the value lists with commas (,). Each value list represents a row of data.

    • values: specifies a single value to be inserted. Each value corresponds to a column. Separate values with commas (,). You can specify an expression (expr) or the DEFAULT keyword to use the default value of the column.

  Example:

  • Insert a row of data into the tbl1 table.

    INSERT INTO tbl1(col1, col2) VALUES(1, 'A1');
    

  • Insert multiple rows of data into the tbl1 table.

    INSERT INTO tbl1(col1, col2) VALUES(2, 'A2'),(3, 'A3');
    

• [(column_list)] select_stmt: selects data from one or more source tables and inserts the query result into the target table.

  • column_list: optional. Specifies the list of column names to which the data is inserted. Separate multiple column names with commas (,).
  • select_stmt: specifies the query statement to be executed. The result of the query statement will be inserted into the target table. select_stmt must return a result set. For more information about the query statement, see SELECT statement.

  Example:

  INSERT INTO tbl1(col1, col2) SELECT col1, col2 FROM tbl2 WHERE col2 = 'a3';
  

• SET update_asgn_list: specifies the values of the columns by using the SET clause and inserts the data into the target table. update_asgn_list specifies the assignment statement. For more information, see update_asgn_list.

  Example:

  INSERT INTO tbl1 SET col1= 4, col2='a4';
  

update_asgn_list

column_name = expr [, ...]: specifies the list of column names and expressions. Used to update existing rows. Separate multiple value pairs with commas (,).

Example:

• Specify that when a key conflict occurs, the value of the col2 column in the target row is updated to the value that was attempted to be inserted (i.e., VALUES(col2), which is a2 in this example).

  INSERT INTO tbl1(col1, col2) VALUES (2, 'a2') ON DUPLICATE KEY UPDATE col2 = VALUES(col2);
  

Examples

The following example defines the test_tbl1 and test_tbl2 tables:

CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT)
    PARTITION BY KEY(col1)
         (PARTITION p0,
          PARTITION p1,
          PARTITION p2,
          PARTITION p3
         );

CREATE TABLE test_tbl2(col1 INT PRIMARY KEY, col2 INT);

• Insert a single row of data into the p0 partition of the test_tbl1 table.

  INSERT INTO test_tbl1 PARTITION(p0) (col1) VALUES(5);
  

  View the data in the test_tbl1 table:

  SELECT * FROM test_tbl1 PARTITION(p0);
  

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    5 | NULL |
  +------+------+
  1 row in set
  

• Insert values into the updatable view v.

  CREATE VIEW v AS SELECT * FROM test_tbl2;
  

  INSERT INTO v VALUES (1, 1);
  

  View the data in the v view:

  SELECT * FROM v;
  

  The result is as follows:

  +------+------+
  | col1 | col2 |
  +------+------+
  |    1 |    1 |
  +------+------+
  1 row in set
  

• INSERT OVERWRITE SELECT example:

  1. Insert data into the test_tbl1 table.

     INSERT INTO test_tbl1 VALUES (1, 30),(2, 25),(3, 22);
     

  2. Use the INSERT OVERWRITE SELECT statement to filter data from the test_tbl1 table where col2 is greater than 25 and insert this data into the test_tbl2 table, replacing its existing content.

     INSERT OVERWRITE test_tbl2 SELECT * FROM test_tbl1 WHERE col2 > 25;
     

  3. View the data in the test_tbl2 table after the data has been replaced.

     SELECT * FROM test_tbl2;
     

     The result is as follows:

     +------+------+
     | col1 | col2 |
     +------+------+
     |    1 |   30 |
     +------+------+
     1 row in set
     

References

• Insert data
• Direct load
• SELECT statement