Purpose
You can use this statement to add one or more records to a table.
Direct INSERT operations on subqueries are not supported. For example, INSERT INTO (SELECT * FROM t1) VALUES(1, 1) cannot be executed.
Syntax
INSERT {into_table_insert | overwrite_table_insert};
into_table_insert:
[/*+ [APPEND | direct(bool, int, [load_mode])] enable_parallel_dml PARALLEL(N) */]
[IGNORE] [INTO]
single_table_insert
[ON DUPLICATE KEY UPDATE update_asgn_list]
load_mode:
'full'
| 'inc_replace'
single_table_insert:
{dml_table_name values_clause
| dml_table_name '(' ')' values_clause
| dml_table_name '(' column_list ')' values_clause
| dml_table_name SET update_asgn_list
| dml_table_name select_stmt}
dml_table_name:
table_name [PARTITION (partition_name,...)]
values_clause:
{{VALUES | VALUE} ({expr | DEFAULT},...) [, ...]
| select_stmt}
column_list
column_name [, ...]
update_asgn_list:
column_name = expr [, ...]
overwrite_table_insert:
[/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt;
Parameters
| Parameter | Description |
|---|---|
| APPEND | direct() | Specifies to enable direct load.
NoticeWe recommend that you do not perform an upgrade during a direct load task because the upgrade may cause the direct load task to fail.
INSERT INTO to import data in direct load mode, see Import data through direct load by using the INSERT INTO SELECT statement. |
| enable_parallel_dml parallel(N) | The degree of parallelism (DOP) for loading data. The default value of N is 4.
NoteGenerally, the |
| IGNORE | Ignores errors that occur during the execution of the INSERT statement. |
| column_list | The name of the column to which the data is inserted. Multiple columns must be separated with commas (,). |
| table_name | The name of the table into which data is to be inserted. |
| partition_name | The name of the partition into which data is to be inserted. Separate multiple partitions with commas (,). |
| update_asgn_list | The statement that assigns values, for example, c1 = 2. |
| ON DUPLICATE KEY UPDATE | The action performed on duplicate primary keys or unique keys. If this parameter is specified, the configured value takes the place of the inserted value when the inserted primary key or unique key is duplicate. If this parameter is not specified, an error is returned when the inserted primary key or unique key is duplicate. |
| overwrite_table_insert | Overwrites existing data in the target table or partition with the query results. For more information about how to use the INSERT OVERWRITE SELECT statement, see the Use the INSERT OVERWRITE SELECT statement to insert data section in the Insert data topic. |
| list_of_columns | The table columns into which data is to be inserted. |
| select_stmt | The SELECT clause. For more information, see SELECT statement. |
Examples
Sample tables and their data are defined as follows:
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
Insert a row of data into Table
t1.obclient> INSERT INTO t1 VALUES(1,1); Query OK, 1 row affected obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | +----+------+ 1 row in setInsert multiple rows of data into Table
t1.obclient> INSERT t1 VALUES(1,1),(2,DEFAULT),(2+2,3*4); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | NULL | | 4 | 12 | +----+------+ 3 rows in setInsert a single row of data into Partition
p0of Tablet1.obclient> INSERT INTO t1 PARTITION(p0) (c1) VALUES(5); Query OK, 1 row affected obclient> SELECT * FROM t1 PARTITION(p0); +----+------+ | c1 | c2 | +----+------+ | 5 | NULL | +----+------+ 1 row in setWhen you insert a duplicate primary key value into Table
t1, useON DUPLICATE KEY UPDATEto update the value.obclient> INSERT INTO t1 VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE c1=100; Query OK, 3 rows affected Records: 2 Duplicates: 1 Warnings: 0 obclient> SELECT * FROM t1; +-----+------+ | c1 | c2 | +-----+------+ | 100 | 1 | +-----+------+ 1 row in setInsert values into an updatable view
v.obclient> CREATE VIEW v AS SELECT * FROM t1; Query OK, 0 rows affected obclient> INSERT INTO v VALUES (1,1); Query OK, 1 row affected obclient> SELECT * FROM v; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | +----+------+ 1 row in setInsert the query results of Table
t1into Tablet2.obclient> INSERT INTO t1 SELECT * FROM t2 WHERE c1>2; Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM t1; +----+------+ | c1 | c2 | +----+------+ | 3 | 3 | | 4 | 4 | +----+------+ 2 rows in set