Insert data|V4.3.3| docs|Distributed Database

Insert data

Last Updated：2025-11-27 02:38:06 Updated

After you create a table, you can use the INSERT statement or other statements to insert records into the table. This topic describes how to use related statements.

Prerequisites

Before you insert data into a table, make sure that:

You have connected to a MySQL tenant of OceanBase Database. For more information about how to connect to OceanBase Database, 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 on.
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 the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Use the INSERT INTO statement to insert data

Use the INSERT INTO statement 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 table into which data is to be inserted.
(list_of_columns)	No	The table columns into which data is to be inserted.
(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 data insertion

Before you insert data, we recommend that you learn the information about all columns of the table, including the column types, valid values, and whether null values are allowed.

You can use the DESC statement to query information about columns.
```
obclient [test]> DESC test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   |     | NULL    |       |
| col2  | int(11) | YES  |     | 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 information_schema.TABLE_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 that the information about the table into which data is to be inserted is as follows:

obclient [test]> CREATE TABLE t_insert(
    id int NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL,
    value int,
    gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
Query OK, 0 rows affected

The id and name columns of the table cannot be empty. The id column is the primary key column with a UNIQUE constraint, and therefore cannot have duplicate values. A default value is specified for the gmt_create column.

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

A default value is specified for the gmt_create column. Therefore, you do not need to specify a value for this column when you insert data.

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

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

If no default value is specified for the gmt_create column, you must specify a value for this column when you insert data by using the following statement:

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

Insert multiple rows of data in batches

To insert multiple records, you can also use an INSERT statement that contains multiple VALUES clauses. A single multi-row insertion statement is executed faster than multiple single-row insertion statements.

The operation in Example 1 can be completed by using the statement in Example 2.

Example 2: Insert multiple rows of data in batches.

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

To back up a table or copy all records in a table to another table, you can use the INSERT INTO ... SELECT ... FROM statement as the VALUES clause in the INSERT statement for batch insertion.

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

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
+----+------+-------+---------------------+
3 rows in set

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

obclient [test]> INSERT INTO t_insert_bak SELECT * FROM t_insert;
Query OK, 2 rows affected

obclient [test]> SELECT * FROM t_insert_bak;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
+----+------+-------+---------------------+
3 rows in set

Avoid UNIQUE constraint conflicts

When you insert identical records into a table that has a UNIQUE constraint, the database returns an error. Here is the error message:

obclient [test]> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

You can use the INSERT IGNORE INTO or INSERT INTO ON DUPLICATE KEY UPDATE statement to avoid this error.

Here are some examples:

If you use INSERT IGNORE INTO to avoid constraint conflicts, you can use the IGNORE keyword to ignore the impact of an insertion failure caused by a constraint conflict.

obclient [test]> INSERT IGNORE INTO t_insert(id, name, value) 
    VALUES (3,'UK', 10003),(4, 'JP', 10004);
Query OK, 1 row affected 

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
|  4 | JP   | 10004 | 2022-10-12 17:02:52 |
+----+------+-------+---------------------+
4 rows in set

In this example, the INSERT IGNORE INTO statement is used. Therefore, the system does not return an error even if (3,'UK', 10003) failed to be inserted.

If you use INSERT INTO ON DUPLICATE KEY UPDATE to avoid constraint conflicts, you can specify the action to take on duplicate primary keys or unique keys.
Note
- When ON DUPLICATE KEY UPDATE column_name = expr is specified, if the data to be inserted is duplicate with values of the primary key or unique key, you can use the column_name = expr statement to update the data in conflicting rows of the table. The column_name = expr statement can assign values to one or more columns of the conflicting rows. Separate the columns with commas (,).
- When ON DUPLICATE KEY UPDATE column_name = expr is not specified, if the data to be inserted is duplicate with values of the primary key or unique key, the system returns an error.
```
obclient [test]> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(5, 'CN', 10005) ON DUPLICATE KEY UPDATE name = VALUES(name);
Query OK, 1 row affected 

obclient [test]> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 16:29:16 |
|  2 | US   | 10002 | 2022-10-12 15:17:17 |
|  3 | UK   | 10003 | 2022-10-12 16:29:26 |
|  4 | JP   | 10004 | 2022-10-12 17:02:52 |
|  5 | CN   | 10005 | 2022-10-12 17:27:46 |
+----+------+-------+---------------------+
5 rows in set
```
In this example, ON DUPLICATE KEY UPDATE name = VALUES(name) specifies that when the inserted data is duplicate with a value of the primary key, the value of the name column of the conflicting row (3,'EN', 10003) in the original data is updated to the value of the name column of the data to be inserted. For non-conflicting rows, data is normally inserted.

Use the INSERT OVERWRITE SELECT statement to insert data

The INSERT OVERWRITE SELECT statement overwrites existing data in the target table or partition with the query results.

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 SELECT statement.
partition_name	The name of the partition into which data is to be inserted. Separate multiple partitions with commas (,).

Limitations on using INSERT OVERWRITE SELECT

Use INSERT OVERWRITE SELECT to insert data into tables

The statement cannot be executed in a multi-row transaction. Therefore, you must first execute the SET autocommit = on; statement to enable auto commit of transactions.
The target table is locked during the execution of this statement. During this process, you cannot initiate any DDL operations on this table. If you initiate a DML operation, it waits for the table lock to be released, or until it times out. However, you can still initiate queries on the table during this process.
The number of columns in the source data to be inserted by the statement must exactly match that in the target table to be overwritten. Otherwise, an error is returned.
The statement writes data in full direct load mode, and is therefore subject to the full direct load feature. For more information about the direct load feature, see Limitations in Import data through direct load by using the INSERT INTO SELECT statement.
If you specify a hint for direct load in this statement, an error is returned.
This statement is subject to the Parallel Data Manipulation Language (PDML) framework. You cannot use this statement to import data in scenarios that do not support PDML. If you do, an error is returned. For more information about PDML, see Parallel DML.

Use INSERT OVERWRITE SELECT to insert data into partitions

Description

The source table can be a partitioned table of any partitioning type or a non-partitioned table.
The target table can be either a partitioned table or subpartitioned table.
The target table can contain a local index or a large object (LOB) column.
You can specify all or partial partitions in the target table. If the target table has subpartitions, you can specify them separately or together with partitions.
If a specified partition in the target table is not hit by data from the source table, data in this partition is cleared and overwritten by null values.

Limitations

If the specified partition does not exist, an error is returned.
In full direct load mode, you do not need to add a /*+ append */ hint. Otherwise, an error is returned.
The statement cannot be executed in a multi-row transaction. Therefore, you must first execute the SET autocommit = on; statement to enable auto commit of transactions.
The target table cannot be a hash- or key-partitioned table. If the target table is a subpartitioned table, no limitations are exerted on the partitions, but the subpartitions cannot be of the hash or key types.
If a specified partition in the target table is not hit by data from the source table after calculation based on the target table partitioning rule, an error is returned to indicate that the specified partition does not exist.
The target table cannot contain auto-increment columns.
The target table cannot contain a global index or a foreign key.
The target table cannot be an external table.

Examples

Example 1

Create two test tables: source_tbl1 that serves as the data source and target_tbl1 that serves as the target table.

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

CREATE TABLE target_tbl1 (col1 INT, col2 VARCHAR(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 the data in 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 select the data whose col3 values exceed 25 from the source_tbl1 table and insert the data into the target_tbl1 table in overwriting mode.
```
INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;
```

Query the data in the target_tbl1 table.

SELECT * FROM target_tbl1;

The return result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | A1   |   30 |
+------+------+------+
1 row in set

Example 2

Use the INSTER OVERWRITE PARTITION SELECT statement to insert data.

Create a partitioned table named t1_ins_overwrt.

obclient> 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, 1 row affected

Create another partitioned table named t2_ins_overwrt.

obclient> 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, 1 row affected

Insert several data records into the two tables, respectively.

obclient> INSERT INTO t1_ins_overwrt values (1, 10), (12, 20), (23, 30), (34, 40), (45, 50);
Query OK, 5 rows affected (0.018 sec)
Records: 5  Duplicates: 0  Warnings: 0

obclient> INSERT INTO t2_ins_overwrt values (3, 60), (8, 80), (17, 170), (26, 260), (29, 290), (142, 1420);
Query OK, 6 rows affected (0.015 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query the data in the p3 partition in each table.

obclient> SELECT * FROM t1_ins_overwrt partition(p3);
+------+------+
| c1   | c2   |
+------+------+
|   34 |   40 |
|   45 |   50 |
+------+------+
2 rows in set (0.006 sec)

obclient> SELECT * FROM t2_ins_overwrt partition(p3);
+------+------+
| c1   | c2   |
+------+------+
|  142 | 1420 |
+------+------+
1 row in set (0.006 sec)

Run the following SQL command to insert data in the p3 partition in the t1_ins_overwrt table into the same partition in the t2_ins_overwrt table.

obclient> INSERT OVERWRITE t2_ins_overwrt partition(p3) select * from t1_ins_overwrt partition(p3);
Query OK, 2 rows affected (1.354 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

obclient> SELECT * FROM t2_ins_overwrt partition(p3);
+------+------+
| c1   | c2   |
+------+------+
|   34 |   40 |
|   45 |   50 |
+------+------+
2 rows in set (0.006 sec)

Use the REPLACE INTO statement to insert data

Besides the INSERT statement, you can use the REPLACE INTO statement instead to insert data into a table without data records or a table with data records but no conflicts with the primary key or unique key constraints. For more information about the REPLACE INTO statement, see REPLACE.

Here is an example:

Create a table named t_replace and then use the REPLACE INTO statement to insert data into the table.

obclient [test]> CREATE TABLE t_replace(
    id int NOT NULL PRIMARY KEY
    , name varchar(10) NOT NULL
    , value int
    ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
);
Query OK, 0 rows affected 

obclient [test]> REPLACE INTO t_replace VALUES(1,'CN',2001, current_timestamp ());
Query OK, 1 row affected 

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-11-23 09:52:44 |
+----+------+-------+---------------------+
1 row in set

Use the REPLACE INTO statement to insert data into the t_replace table that has data records.

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-03-22 16:13:55 |
+----+------+-------+---------------------+
1 row in set 

obclient [test]> REPLACE INTO t_replace values(2,'US',2002, current_timestamp ());
Query OK, 1 row affected

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-11-23 09:52:44 |
|  2 | US   |  2002 | 2022-11-23 09:53:05 |
+----+------+-------+---------------------+
2 rows in set