Description
You can execute the INSERT statement to add one or more records to a table.
Syntax
INSERT [hint_options] single_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 [, ...]
into_clause:
{ INTO into_var_list | BULK COLLECT INTO into_var_list}
into_var_list:
{ USER_VARIABLE | ref_name } [, ...]
Parameters
| Parameter | Description |
|---|---|
| hint_options | The hint. |
| insert_table_clause | The table to insert. You can specify a base table, an updatable view, or a special subquery. |
| opt_nologging | Minimize the number of logs generated when you insert data. |
| column_list | The columns to insert. |
| returning_exprs | The projection after you insert data. |
| into_clause | Insert the updated column values to the specified table columns. |
Notice
A special subquery is similar to a subquery in an updatable view. Such a subquery cannot include complex operators, such as GROUP BY, DISTINCT, and WINDOW FUNCTION.
Examples
In the following examples, table t1 is used to describe how to insert data into a single table.
OceanBase(admin@test)>create table t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
OceanBase(admin@test)>select * from t1;
Empty set (0.02 sec)
- Insert a row into table t1.
OceanBase(admin@test)>insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.04 sec)
- Use a subquery to insert data into table t1.
OceanBase(admin@test)>insert into (select * from t1) values(1,1);
Query OK, 1 row affected (0.01 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| C1 | C2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.01 sec)
- Execute a statement that includes the RETURNING clause to insert data into table t1.
OceanBase(admin@test)>insert into t1 values(1,1) returning c1;
+----+
| C1 |
+----+
| 1 |
+----+
1 row in set (0.02 sec)
OceanBase(admin@test)>select * from t1;
+----+------+
| C1 | C2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.01 sec)