INSERT

Purpose

This statement adds 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 destination table.
• To execute the INSERT ... SELECT ... statement, you must have the INSERT privilege on the destination table and the SELECT privilege on the source table (the table specified in the SELECT clause).

For more information about privileges in 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. Use this hint to enable direct load.

    Notice

    During the execution of a direct load task, it is not recommended to perform an upgrade of 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: Indicates whether the data to be written needs to be sorted. Valid values:

        • true: The data needs to be sorted.
        • false: The data does not need to be sorted.

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

      • load_mode: Optional. Indicates the direct load mode. Valid values:

        • full: Default value. Full load.

        • inc: Incremental load. Supports the INSERT and IGNORE semantics.

        • inc_replace: Incremental load without checking for duplicate primary keys. Equivalent to incremental load with the REPLACE semantics.

          Notice

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

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

  • enable_parallel_dml parallel(N): Optional. The degree of parallelism for data loading.

    Note

    Generally, the enable_parallel_dml hint and the parallel hint must be used together to enable parallel DML. However, if the schema of the target table specifies a table-level parallelism, only the enable_parallel_dml hint is required.

• NO_DIRECT: Forces a single SQL statement to not use direct load. If the input SQL statement contains this hint, the entire statement ignores other direct load hints and executes a regular load.

For more information about hints, see Optimizer Hint.

single_table_insert

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

  • column_list: Optional. Specifies the list of column names to which the data will be inserted. Multiple column names are separated by 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 (()). Multiple value lists can be specified, separated by commas (,), with each value list representing a row of data.

    • values: The individual value to be inserted, corresponding to a column. Values are separated by commas (,). It can be an expression (expr) or the DEFAULT keyword to use the column's default value.

  Example:

  • Insert a single 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 will be inserted. Multiple column names are separated by commas (,).
  • select_stmt: Specifies the query statement to be executed. The results of this query will be 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.

  Example:

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

• SET update_asgn_list: Specifies the values for each column using the SET clause, and inserts the data into the target table. update_asgn_list represents the assignment statement. For more information, see update_asgn_list below.

  Example:

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

update_asgn_list

column_name = expr [, ...]: A list of column names equal to expressions, used to update existing rows. Multiple value pairs are separated by commas (,).

Example:

• Specifies that, in the event of a key conflict, the value of the col2 column in the target row should be updated to the value originally 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 tables test_tbl1 and test_tbl2:

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 table test_tbl1.

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

  View the data in table test_tbl1:

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

  SELECT * FROM v;
  

  The result is as follows:

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

• INSERT OVERWRITE SELECT example:

  1. Insert data into table test_tbl1.

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

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

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

  3. View the data in table test_tbl2 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
• Bypass import
• SELECT statement