Skip to main content

INSERT

Description​

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

Permissions​

  • 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 seekdb, see Privilege types of seekdb.

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​

ParameterDescription
into_table_insertUsed to insert data into a table.
hint_optionsOptional. Specifies the Hint options. For more information, see hint_options.
IGNOREOptional. Indicates that errors that occur during data insertion are ignored.
INTOOptional. Indicates the target of the insertion. This parameter can be omitted.
single_table_insertSpecifies how to insert data into a table. Currently, three data insertion methods are supported. For more information, see single_table_insert.
table_nameSpecifies the name of the table into which data is to be inserted.
PARTITION (partition_name, ...)Optional. Specifies the partition into which data is to be inserted. When multiple partitions are specified, separate them with commas (,). partition_name specifies the name of the partition into which data is to be inserted.
column_listOptional. Specifies the columns into which data is to be inserted. When multiple columns are specified, separate them with commas (,).
column_nameThe name of the column into which data is to be inserted.
ON DUPLICATE KEY UPDATE update_asgn_listOptional. Specifies the operation to be performed on duplicate primary keys or unique keys.
  • If ON DUPLICATE KEY UPDATE is specified, the configured values will replace the values to be inserted when the primary key or unique key being inserted is duplicated.
  • If ON DUPLICATE KEY UPDATE is not specified, an error will be returned when the primary key or unique key being inserted is duplicated.
update_asgn_list specifies the assignment statement. For more information, see update_asgn_list.
overwrite_table_insertUsed to replace existing data in a table or partition with the query result. For more information about the INSERT OVERWRITE SELECT statement, see the Use the INSERT OVERWRITE SELECT statement section in Insert data.
select_stmtSpecifies the SELECT clause. For more information about query statements, see SELECT statement.

hint_options​

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

    • APPEND | DIRECT(bool, int, [load_mode]): optional. Uses the hint to enable the direct import feature.

      tip

      When a bypass import task is being executed, do not upgrade seekdb at the same time because this may cause the bypass import task to fail.

      • APPEND: By default, it is equivalent to using DIRECT(true, 0). You can specify the GATHER_OPTIMIZER_STATISTICS option to perform online statistics collection.

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

        • bool: indicates whether the data to be written is sorted. Valid values:

          • true: Specifies that a sort is needed.
          • false: Indicates that sorting is not required.

        • int: the maximum number of error lines that you can tolerate.

        • load_mode is an optional parameter that specifies the mode of bypass import. The value of the parameter must be enclosed with English single quotes, and the valid value is:

          • full: specifies a full import. Default value.

          • inc: specifies incremental import, which supports the INSERT and IGNORE semantics.

          • inc_replace: Specifies incremental import without checking for duplicate primary keys, equivalent to incremental import with REPLACE semantics.

            tip

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

          For more information about bypassing data import using the INSERT INTO SELECT clause, see Overview.

    • enable_parallel_dml parallel(N)：The parallelism for data loading. This is an optional parameter.

      info

      Generally, you must specify both enable_parallel_dml and parallel hints to enable parallel DML. However, if the parallel degree is specified at the table level, you must only specify the enable_parallel_dml hint.

  • NO_DIRECT: This option prevents a single SQL statement from using a direct load, and it ignores other direct load hints specified for this SQL statement. If the SQL statement contains the NO_DIRECT option, it is treated as a normal load.

For more information about hints, see Optimizer Hint.

single_table_insert​

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

    • column_list: optional. Specifies the list of column names corresponding to the inserted data. The column names are separated by commas (,).

    • VALUES | VALUE: The keyword used to indicate the list of values to be inserted.

    • values_list: the list of data values to be inserted, enclosed in parentheses. You can specify multiple lists of values. Each value list separated with a comma indicates a data row.

      • values: A single value that you insert for a column. The values that you specify for the columns are separated by commas (,). You can specify expr or DEFAULT to use the default value of a column.

    Example:

    • Insert a row of data into tbl1.

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

    • Insert multiple rows into the tbl1 table.

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

  • [(column_list)] select_stmt: Queries data from one or more source tables and inserts the query results to a target table.

    • column_list: specifies the list of column names to which data is to be inserted. Multiple column names are separated by commas (,). This parameter is optional.
    • select_stmt: specifies the query statement to be executed, whose result set is to be inserted into the target table. select_stmt must return a result set. For more information about the specific structure and options of a query statement, see SELECT statement.

    Example:

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

  • SET update_asgn_list: Specifies column values using the SET clause to insert data into the destination table. update_asgn_list represents an assignment statement; for more details, see update_asgn_list.

    Example:

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

update_asgn_list​

column_name = expr [, ...]: The list of column names to be updated and their corresponding expressions, separated by commas (,).

Example:

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

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

Examples​

The test_tbl1 and test_tbl2 tables are defined as follows:

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 return result is as follows:

    +------+------+
    | col1 | col2 |
    +------+------+
    |    5 | NULL |
    +------+------+
    1 row in set (0.001 sec)

  • 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 return result is as follows:

    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |    1 |
    +------+------+
    1 row in set (0.001 sec)

  • 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 the filtered data into the test_tbl2 table to replace 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 return result is as follows:

      +------+------+
      | col1 | col2 |
      +------+------+
      |    1 |   30 |
      +------+------+
      1 row in set (0.001 sec)

References​

Contents

Edit this page