INSERT|V4.3.1| docs|Distributed Database

INSERT

Last Updated：2024-06-28 05:30:31 Updated

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

Parameter	Description
hint_options	Optional. The hint options. For more information about hints, see Overview. OceanBase Database in Oracle mode allows you to use the `direct` hint in the `INSERT INTO SELECT` statement to enable direct load. The syntax is as follows: `/+ direct(bool, int, load_mode)} enable_parallel_dml PARALLEL(N) /`. `bool`: specifies whether to sort the data written. The value `true` indicates that sorting is needed, and the value `false` indicates that sorting is not needed. `int`: the maximum number of erroneous rows allowed. `load_mode`: Optional. The direct load mode. Enclose the value with single quotation marks. Valid values: `full`: the full direct load mode. This is the default value. `inc_replace`: the incremental direct load mode based on the `replace` semantic. Notice The incremental direct load feature is currently in the experimental stage. To ensure system stability, we recommend that you do not use it in a production environment. `enable_parallel_dml parallel(N)`: The DOP for loading data. The default value of `N` is 4. Note Generally, the `enable_parallel_dml` and `parallel` hints must be used together to enable PDML. However, if a table-level DOP is specified for the schema of the target table, you only need to specify the `enable_parallel_dml` hint. For more information about how to use `INSERT INTO` to import data in bypass mode, see Import data in bypass mode by using the INSERT INTO SELECT statement.
single_table_insert	Inserts data into a single table.
insert_table_clause	The table into which the records are inserted, which can be a base table, an updatable view, or a special subquery.
opt_nologging	Minimizes the number of logs generated by an insert.
column_list	The name of the column into which the records are inserted.
returning_exprs	Returns the projected column into which the records are inserted.
insert_into_clause	Inserts the column into which the records are inserted into the specified table.
multi_table_insert	Inserts data into multiple tables.
conditional_insert_clause	Conditionally inserts data into multiple tables. `ALL`: traverses all the conditional branches and inserts data into the corresponding tables that meet the condition. `FIRST`: inserts data into the table corresponding to the first conditional branch that is met and ignores the remaining branches.
error_logging_clause	Saves the SQL errors and the values in the columns of affected rows in an error record table.

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

Sample tables and their data are defined as follows:

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

Specify the load_mode parameter of the direct hint to perform an incremental direct load.

obclient [SYS]> INSERT /*+ direct(true, 0, 'inc_replace') enable_parallel_dml parallel(2) */ INTO tbl2 SELECT * FROM tbl1;

The return result is as follows:

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

Previous topic

DELETE

Last

Next topic

MERGE

INSERT

Purpose

Syntax

Parameters

Notice

Note

Notice

Examples