Insert data|V4.3.5| docs|Distributed Database

Insert data

Last Updated：2025-07-15 08:58:43 Updated

After a table is created, you can execute the INSERT statement or other statements to insert rows into the table. This topic explains how to use these statements.

Prerequisites

Before you insert data, make sure that:

You have connected to an Oracle tenant of the database. For more information, see Connection methods.

Note

You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged in.
You have the INSERT privilege on the target table. For more information about how to view your privileges, see View user privileges. If you do not have this privilege, contact the administrator to obtain the privilege. For more information, see Grant direct privileges.

Insert data by using the INSERT INTO statement

You can execute the INSERT statement and follow the suggestions below to insert data into a table.

The syntax of the INSERT INTO statement is as follows:

INSERT INTO table_name [(list_of_columns)] VALUES (list_of_values);

Parameter	Required?	Description
table_name	Yes	The name of the table where you want to insert data.
(list_of_columns)	No	The columns where you want to insert data.
(list_of_values)	Yes	The values of the columns specified by `list_of_columns`. The values and columns must be in one-to-one mapping.

Suggestions on inserting data

Before you insert data, learn about all columns in the table, including the column types, valid values, and whether null values are allowed.

You can query column information by executing the DESC statement.
```
obclient [SYS]> DESC ordr;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER       | YES  | NULL | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set
```
- For a NOT NULL column, note that:
  - If the column has a default value, you do not need to specify a value for this column when you insert data. The system inserts the default value into this column if you do not specify a value.
  - If no default value has been specified for the column, you must specify a value for the column when you insert data.
- For a NULL column, you do not need to specify a value for this column when you insert data. The system inserts a NULL value into this column.
Before you insert data into a table, we recommend that you learn the information about constraints defined on columns of the table. This is to avoid errors when you insert data.

You can use the DESC statement to query NOT NULL, PRIMARY KEY, and UNIQUE constraints. You can query the ALL_CONSTRAINTS, DBA_CONSTRAINTS, or USER_CONSTRAINTS view for FOREIGN KEY and CHECK constraints.

Insert a single row of data

You can use the INSERT statement to insert a single row of data. To insert multiple records, you can execute the single-row insertion statement multiple times. To perform batch insertion, see the Insert multiple rows of data in batches section.

Assume the table for inserting data has the following structure:

obclient [SYS]> CREATE TABLE t_insert(
    id number NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL, 
    value number,
    gmt_create DATE NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected

In this table, the id and name columns cannot be null. The id column is the primary key column, which inherently enforces a UNIQUE constraint, and therefore cannot have duplicate values. The gmt_create column has a default value.

Example 1: Insert multiple rows of data by executing the single-row insertion statement multiple times.

Since the gmt_create column has a default value, you can omit it when inserting data.

obclient [SYS]> INSERT INTO t_insert(id, name, value) 
VALUES (1,'CN',10001);
Query OK, 2 rows affected

obclient [SYS]> INSERT INTO t_insert(id, name, value) 
VALUES(2,'US', 10002);
Query OK, 2 rows affected

Note that if the gmt_create column does not have a default value, you must specify a value when inserting data. The statement would be as follows:

obclient [SYS]> INSERT INTO t_insert(id, name, value, gmt_create)
 VALUES (3,'EN', 10003, sysdate);
Query OK, 1 row affected

Insert multiple rows of data in batches

To insert multiple records, you can use a single INSERT statement with multiple VALUES clauses. A single multi-row insertion statement is more efficient than executing multiple single-row insertion statements.

The operation shown in Example 1 can also be completed with the batch insertion statement in Example 2.

Example 2: Insert multiple rows of data in batches.

obclient [SYS]> INSERT INTO t_insert(id, name, value) 
  VALUES (1,'CN',10001),(2,'US', 10002);
Query OK, 2 rows affected

When you need to back up a table or copy all records from one table to another, you can use the INSERT INTO ... SELECT ... FROM statement. This allows you to use the results of a query as the VALUES clause for batch insertion.

Example 3: Back up all data from the t_insert table to the t_insert_bak table.

obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 31-OCT-22  |
|  2 | US   | 10002 | 31-OCT-22  |
|  3 | EN   | 10003 | 31-OCT-22  |
+----+------+-------+------------+
3 rows in set

obclient [SYS]> CREATE TABLE t_insert_bak(
    id number NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL, 
    value number,
    gmt_create DATE NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected 

obclient [SYS]> INSERT INTO t_insert_bak SELECT * FROM t_insert;
Query OK, 2 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [SYS]> SELECT * FROM t_insert_bak;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 31-OCT-22  |
|  2 | US   | 10002 | 31-OCT-22  |
|  3 | EN   | 10003 | 31-OCT-22  |
+----+------+-------+------------+
3 rows in set

Use the INSERT OVERWRITE SELECT statement to insert data

The INSERT OVERWRITE SELECT statement overwrites existing data in the target table with the query results. This operation is atomic. If any exception occurs during this operation, it is rolled back as a whole.

The syntax is as follows:

INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt;

Parameter	Description
PARALLEL(N)	Optional. The degree of parallelism (DOP) for overwriting. If this parameter is not specified, the default DOP `2` is used.
table_name	The name of the table into which data is to be inserted.
list_of_columns	The table columns into which data is to be inserted.
select_stmt	The `SELECT` clause. For more information, see SIMPLE SELECT.
partition_name	The name of the partition into which data is to be inserted. Separate multiple partitions with commas (,).

Limitations on INSERT OVERWRITE SELECT

Limitations on table-level INSERT OVERWRITE SELECT

This statement cannot be executed within a multi-row transaction. To enable its execution, you must first run the SET autocommit = on; command to enable auto-commit mode.
The target table is locked during execution. Concurrent DDL operations on the table are not allowed. Concurrent DML operations will wait for the lock to be released or time out. However, queries on the table are permitted during this process.
The number of columns in the source data must match exactly with the columns in the target table for overwrite operations. Otherwise, an error will occur.
This statement writes data using the full direct load method and is subject to its limitations. For more information, see the Limitations section in Import data through direct load by using the INSERT INTO SELECT statement.
Specifying a direct load hint in this statement will result in an error.
The statement is subject to the limitations of the Parallel Data Manipulation Language (PDML) framework. It cannot be used in scenarios where PDML is unsupported. Attempting to do so will return a "not supported" error. For more information about parallel DML, see Parallel DML.

Limitations on partition-level INSERT OVERWRITE SELECT

Overview

The source table can be either partitioned or non-partitioned, with no restrictions on the type of partitioning.
The target table can be either a partitioned table or subpartitioned table.
The target table can include local indexes and LOBs.
You can specify all or some of the partitions in the target table. For subpartitions, you can specify partitions, subpartitions, or a mix of both.
If data from the source table, based on the target table's partitioning rules, does not match any of the specified partitions in the target table, an error will occur, indicating that the partition does not exist.
If no data from the source table matches the specified partitions in the target table, the data in those partitions will be cleared (overwritten with empty data).
Starting with OceanBase Database V4.3.5 BP1, partition-level INSERT OVERWRITE operations support target tables where the last-level partitions use Hash or Key partitioning.

Limitations

The specified partitions must already exist. Otherwise, an error will occur.
In full direct load mode, you do not need to add a /*+ append */ hint. Otherwise, an error will occur.
This statement cannot be used in multi-row transactions. To ensure successful execution, you must first enable auto-commit mode by running the SET autocommit = on; command.
Target tables with identity columns are not supported.
Target tables with global indexes or foreign keys are not supported.
External tables cannot be used as target tables.

INSERT OVERWRITE SELECT examples

Example 1

Execute the following SQL statement to enable auto-commit transactions:
```
SET autocommit = on;
```

Create two test tables: source_tbl1 as the source table, and target_tbl1 as the target table.

CREATE TABLE source_tbl1 (col1 INT, col2 VARCHAR2(20), col3 INT);

CREATE TABLE target_tbl1 (col1 INT, col2 VARCHAR2(20), col3 INT);

Insert sample data into the source_tbl1 table.

INSERT INTO source_tbl1 VALUES (1, 'A1', 30),(2, 'B2', 25),(3, 'C3', 22);

Insert sample data into the target_tbl1 table.

INSERT INTO target_tbl1 VALUES (4, 'D4', 35),(5, 'E5', 28);

Query data from the target_tbl1 table.

SELECT * FROM target_tbl1;

The return result is as follows:

+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    4 | D4   |   35 |
|    5 | E5   |   28 |
+------+------+------+
2 rows in set

Use the INSERT OVERWRITE SELECT statement to filter data from source_tbl1 where col3 is greater than 25, and replace the existing content in target_tbl1 with the filtered data.
```
INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;
```

Query data from the target_tbl1 table after the replacement.

SELECT * FROM target_tbl1;

The return result is as follows:

+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
|    1 | A1   |   30 |
+------+------+------+
1 row in set

Example 2

Observe the following partition-level INSTER OVERWRITE PARTITION example:

Execute the following SQL statement to enable auto-commit transactions.
```
obclient [SYS]> SET autocommit = on;
```

Execute the following SQL statement to create a partitioned table named t1_ins_overwrt.

obclient [SYS]> create table t1_ins_overwrt (c1 int primary key, c2 int) partition by range(c1)(
       partition p0 values less than(10),
       partition p1 values less than(20),
       partition p2 values less than(30),
       partition p3 values less than (MAXVALUE)
     );
Query OK, 0 rows affected (0.223 sec)

Execute the following SQL statement to create another partitioned table named t2_ins_overwrt.

obclient [SYS]> create table t2_ins_overwrt (c1 int primary key, c2 int) partition by range(c1)(
       partition p0 values less than(10),
       partition p1 values less than(20),
       partition p2 values less than(30),
       partition p3 values less than (MAXVALUE)
     );
Query OK, 0 rows affected (0.092 sec)

Insert several records into the two tables.

obclient [SYS]> insert into t1_ins_overwrt values (12, 20), (34, 40), (45, 50);
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

obclient [SYS]> insert into t2_ins_overwrt values (3, 60), (8, 80), (17, 170), (26, 260), (29, 290), (142, 1420);
Query OK, 6 rows affected (0.003 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query the data in the p1 partition of the two tables.

obclient [SYS]> select * from t1_ins_overwrt partition(p1);
+------+------+
| C1   | C2   |
+------+------+
|   12 |   20 |
+------+------+
1 row in set (0.006 sec)

obclient [SYS]> select * from t2_ins_overwrt partition(p1);
+------+------+
| C1   | C2   |
+------+------+
|   17 |  170 |
+------+------+
1 row in set (0.007 sec)

Execute the following SQL statement to insert the data in the p1 partition of the t1_ins_overwrt table into the p1 partition of the t2_ins_overwrt table.

obclient [SYS]> insert overwrite t2_ins_overwrt partition(p1) select * from t1_ins_overwrt partition(p1);
Query OK, 3 rows affected (1.360 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query the data in the p1 partition in the t2_ins_overwrt table to verify that the original data has been overwritten.

obclient [SYS]> SELECT * FROM t2_ins_overwrt partition(p1);
+------+------+
| C1   | C2   |
+------+------+
|   12 |   20 |
+------+------+
1 row in set (0.020 sec)

Example 3

Create a table named tbl1 with partitions.

obclient [SYS]> CREATE TABLE tbl1 (
 col1 INT,
 col2 INT
 )
 PARTITION BY RANGE (col1)
 SUBPARTITION BY HASH (col2)
 SUBPARTITIONS 3
 (
     PARTITION p0 VALUES LESS THAN (10),
     PARTITION p1 VALUES LESS THAN (20)
 );
 Query OK, 0 rows affected (0.093 sec)

Insert two records into the tbl1 table.

obclient [SYS]>  INSERT INTO tbl1 (col1, col2) VALUES (2, 3);
                 INSERT INTO tbl1 (col1, col2) VALUES (15, 2);

Query whether data has been inserted into the p0 and p1 partitions of the tbl1 table.

obclient [SYS]> SELECT * FROM tbl1 PARTITION (p0);

The query result is as follows:

+------+------+
| col1 | col2 |
+------+------+
|    2 |    3 |
+------+------+

obclient [SYS]> SELECT * FROM tbl1 PARTITION (p1);

The query result is as follows:

 +------+------+
 | col1 | col2 |
 +------+------+
 |   15 |    2 |
 +------+------+

Create a table named tbl2 with partitions.

obclient [SYS]> CREATE TABLE tbl2 (
 col1 INT,
 col2 INT
 )
 PARTITION BY RANGE (col1)
 SUBPARTITION BY HASH (col2)
 SUBPARTITIONS 3
 (
     PARTITION p0 VALUES LESS THAN (10),
     PARTITION p1 VALUES LESS THAN (20)
 );

Insert two records into the tbl2 table.

obclient [SYS]> INSERT INTO tbl2 (col1, col2) VALUES (0, 22);
                INSERT INTO tbl2 (col1, col2) VALUES (18, 6);

Query whether data has been inserted into the p0 and p1 partitions of the tbl2 table.

obclient [SYS]> SELECT * FROM tbl2 PARTITION (p0);

The query result is as follows:

 +------+------+
 | COL1 | COL2 |
 +------+------+
 |    0 |   22 |
 +------+------+

obclient [SYS]> SELECT * FROM tbl2 PARTITION (p1);

The query result is as follows:

 +------+------+
 | COL1 | COL2 |
 +------+------+
 |   18 |   6 |
 +------+------+

Use the insert overwrite statement to overwrite the data in the p1 partition of the tbl1 table with the data in the tbl2 table.
```
obclient [SYS]> INSERT INTO tbl2 PARTITION (p1) SELECT * FROM tbl1 PARTITION (p1);
```
Verify whether the data in the p1 partition of the tbl2 table has been overwritten.
```
obclient [SYS]> SELECT * FROM tbl2 PARTITION (p1);
```
The query result is as follows:
```
 +------+------+
 | col1 | col2 |
 +------+------+
 |   15 |    2 |
 +------+------+
```
The result shows that the data in the p1 partition of the tbl2 table has been replaced.

Insert data by using the MERGE INTO statement

In a batch processing task, you may need to perform a large number of INSERT operations on a target table. You can also use the MERGE INTO statement to insert records from a source table to a target table when the records to be inserted do not exist in the table. For more information, see Replace data.

Note

When you use the INSERT statement to insert data and the table has a unique constraint, the system returns an error if you attempt to insert the same record. In this case, you can use the MERGE INTO statement to update table records to avoid unique constraint conflicts.

Insert data into a remote table by using a DBLink

OceanBase Database allows you to write data into OceanBase Database in Oracle mode and into Oracle Database through a DBLink.

For example, you can execute the following statement to insert a row (11,11) into a table named t1 in a remote database:

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

obclient> INSERT INTO t1@ob_dblink VALUES (11,11);
Query OK, 1 row affected

obclient> commit;
Query OK, 0 rows affected

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
|   11 |   11 |
+------+------+
2 rows in set

In Oracle mode, OceanBase Database also supports writing data from a local table to a remote table through a DBLink by using the INSERT, DELETE, UPDATE, and MERGE INTO statements. For more information, see Use a DBLink to modify data in a remote database.

Insert data

Prerequisites

Note

Insert data by using the INSERT INTO statement

Suggestions on inserting data

Insert a single row of data

Insert multiple rows of data in batches

Use the INSERT OVERWRITE SELECT statement to insert data

Limitations on INSERT OVERWRITE SELECT

Limitations on table-level INSERT OVERWRITE SELECT

Limitations on partition-level INSERT OVERWRITE SELECT

INSERT OVERWRITE SELECT examples

Example 1

Example 2

Example 3

Insert data by using the MERGE INTO statement

Note

Insert data into a remote table by using a DBLink

References