All Products
Search
Document Center

INSERT

Last Updated: Jun 18, 2021

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)