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, INSERT INTO (SELECT * FROM t1) VALUES(1, 1).

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 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. Specifies the hint to enable direct load.

    Notice

    During the execution of a direct load task, we recommend that you do not upgrade OceanBase Database, because the upgrade 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: The data needs to be sorted.
        • false: The data does not need to be sorted.

      • int: Specifies the maximum number of rows that can be ignored.

      • 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. Indicates full direct load.

        • inc: Indicates incremental direct load, which supports the INSERT and IGNORE semantics.

        • inc_replace: Indicates incremental direct load, which does not check for duplicate primary keys. This is equivalent to incremental direct load with the REPLACE semantics.

          Notice

          When load_mode is set to inc_replace, the IGNORE keyword cannot be used in an INSERT statement.

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

  • enable_parallel_dml parallel(N): Optional. Specifies the parallelism of data loading.

    Note

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

• NO_DIRECT: Specifies that a single SQL statement must not use direct load. If an SQL statement contains this hint, it ignores other direct load hints and executes a normal load.

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 to be inserted. Separate multiple column names with commas (,).

  • VALUES | VALUE: A keyword used to introduce the list of values to be inserted.

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

    • values: 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.

  Here is an 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 results into the target table.

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

  Here is an example:

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

• SET update_asgn_list: Specifies the values of the columns to be inserted into the target table using the SET clause. update_asgn_list specifies the assignment statements. For more information, see update_asgn_list.

  Here is an example:

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

update_asgn_list

column_name = expr [, ...]: A list of column names and expressions, which are used to update existing rows. Separate value pairs with commas (,).

Here is an example:

• When a key conflict occurs, update the value of the col2 column in the target row 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 returned 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 returned 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 original 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 returned result is as follows:

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

References

• Insert data
• Bypass import
• SELECT statement