After a table is created, you can use the INSERT statement or other statements to insert row records into the table. This topic describes how to use these statements and provides examples.
Preparations for data insertion
Before inserting data, confirm the following:
Ensure you are connected to the database as a user tenant. For more information about connecting to a database, see Overview of connection methods.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant to confirm the tenant mode of the current logged-in tenant.Ensure you have the
INSERTprivilege on the table to be operated on. For more information about checking user privileges, see View user privileges. If you do not have this privilege, contact the administrator to grant the privilege. For more information about granting privileges to users, see Grant privileges directly.
Insert data using the INSERT INTO statement
Use the INSERT statement, and refer to the following suggestions 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 into which data is to be inserted. |
| (list_of_columns) | No | The columns in the table into which data is to be inserted. |
| (list_of_values) | Yes | The values of the columns specified by list_of_columns, which must be in one-to-one mapping. |
Suggestions for inserting data
Before inserting data, it is recommended to understand all column information about the table, including column types, valid values, and whether NULL values are allowed.
You can view column information using the
DESCstatement.obclient [test]> DESC test; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | col1 | int(11) | NO | | NULL | | | col2 | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in setIf the column attribute is
NOT NULL:If the column has a default value, you can insert without specifying the value for that column, and the system will insert the default value.
If the column has no default value, you must specify a value when inserting.
If the column attribute is
NULL, you can insert without specifying the value for that column, and the system will insert aNULLvalue.
Before inserting data, it is recommended to understand the constraint definitions on the columns of the table to avoid errors during insertion.
You can view
NOT NULL,PRIMARY KEY, andUNIQUEconstraints using theDESCstatement. To viewFOREIGN KEYandCHECKconstraints, query theinformation_schema.TABLE_CONSTRAINTSview.
Insert a single row of data
You can use the INSERT statement to insert a single row of data. To insert multiple records, execute multiple single-row insertion statements. For batch insertion, refer to Batch insert multiple rows of data.
Assume the table into which data is to be inserted is defined 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
In the table, the id and name columns cannot be null. The id column is the primary key and must be unique; duplicate values are not allowed. The gmt_create column has a specified default value.
Example 1: Insert multiple rows of data using multiple single-row INSERT statements.
Since the gmt_create column has a specified default value, you can omit it when inserting 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
Note that if the gmt_create column does not have a specified default value, you must provide a value when inserting data.
obclient [test]> INSERT INTO t_insert(id, name, value, gmt_create)
VALUES (3,'EN', 10003, current_timestamp ());
Query OK, 1 row affected
Batch insert multiple rows of data
When inserting data, if you need to add multiple records, you can use one INSERT statement with multiple VALUES clauses for batch insertion. A single multi-row INSERT statement is faster than multiple single-row INSERT statements.
The operation in Example 1 can also be completed with the following statement.
Example 2: Batch insert multiple rows of data.
obclient [test]> INSERT INTO t_insert(id, name, value)
VALUES (1,'CN',10001),(2,'US', 10002);
Query OK, 2 rows affected
Furthermore, when backing up table data or copying all records from one table to another, you can use the query statement INSERT INTO ... SELECT ... FROM as the INSERT values clause for batch insertion.
Example 3: Back up all data from table t_insert to table t_insert_bak.
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 uniqueness constraint conflicts
When a table has a uniqueness constraint, inserting duplicate records will cause the database to report an error. The error message is as follows:
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'
This error can be avoided using the INSERT IGNORE INTO statement or the INSERT INTO ON DUPLICATE KEY UPDATE statement.
Examples:
When avoiding constraint conflicts with the INSERT statement, the
IGNOREkeyword ignores the impact of theINSERTfailure caused by the 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 setIn this example, the
INSERT IGNORE INTOstatement is used. The row(3,'UK', 10003)fails to be inserted, but the system does not report an error.When avoiding constraint conflicts with the INSERT statement, you can specify the subsequent processing for duplicate primary keys or unique keys.
Note
- Specify
ON DUPLICATE KEY UPDATE column_name = expr: When the primary key or unique key to be inserted is duplicate, you can use thecolumn_name = exprassignment statement to update the data in the conflicting row in the table. Thecolumn_name = exprassignment statement can assign values to one or more columns of the conflicting row. When assigning values to multiple columns, separate the columns with commas. - If
ON DUPLICATE KEY UPDATE column_name = expris not specified: When the primary key or unique key to be inserted is duplicate, an error is reported during the data insertion.
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 setIn the example,
ON DUPLICATE KEY UPDATE name = VALUES(name)means that when the inserted data duplicates a primary key value in the table, it updates the value of thenamecolumn in the conflicting row's original data(3,'EN', 10003)with the currentnamecolumn data to be inserted. Other non-conflicting rows are inserted normally.- Specify
Insert data using the INSERT OVERWRITE SELECT statement
The INSERT OVERWRITE SELECT statement overwrites the existing data in the target table or partition with the query results.
The syntax of this statement is as follows:
INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt;
Parameter |
Description |
|---|---|
| PARALLEL(N) | Optional. Specifies the degree of parallelism for overwrite operations. If not specified, the default parallelism of 2 is used. |
| table_name | Specifies the name of the table into which to insert. |
| list_of_columns | The columns in the table into which data is to be inserted. |
| select_stmt | Specifies the SELECT clause. For more information about query statements, see SELECT. |
| partition_name | The name of the partition into which data is to be inserted. Separate multiple partitions with commas (,). |
Limitations of INSERT OVERWRITE SELECT
Limitations of table-level INSERT OVERWRITE SELECT
- This statement cannot be used in multi-row transactions. Therefore, to ensure the operation proceeds smoothly, you must first execute the
SET autocommit = on;command to enable automatic transaction commit. - A table lock is acquired on the write table. Concurrent DDL operations on the same table are not allowed. Concurrently initiated DML operations will wait for the table lock to be released until it times out. Queries on the table are allowed during the operation.
- The number of columns that are overwritten in the source and target tables for this statement must exactly match; otherwise, an error will be reported.
- The data write operation of this statement is a full direct load method, so the operation is subject to the limitations of the full direct load feature. For information about direct load, see the Limitations section in Use the INSERT INTO SELECT statement for direct load.
- Specifying the direct load hint for this statement will cause an error.
- Due to the limitations of the PDML (Parallel Data Manipulation Language) framework, data cannot be imported for scenarios not supported by PDML, and
INSERT OVERWRITE SELECTwill report an error "not supported". For more information about parallel DML, see Parallel DML.
Partition-level INSERT OVERWRITE SELECT
Overview
- The source table can be a partitioned table or a non-partitioned table, and there are no requirements for the partition type.
- The target table can be a partitioned table or a subpartitioned table.
- The target table can have local indexes and LOBs.
- You can specify all or part of the partitions of the target table. If the target table has subpartitions, you can specify partitions and subpartitions separately or in combination.
- If the specified target table partition is not hit by any data from the source table, the data in that partition will be cleared (overwritten with empty data).
- Partition-level
insert overwriteoperations support the target table's last-level partition being a hash or key partition.
Limitations
- The specified partition must already exist; otherwise, an error will be reported.
- Based on full direct load, there is no need to add the
/*+ append */hint; otherwise, its use will cause an error. - This statement cannot be used in multi-row transactions. Therefore, to ensure the operation proceeds smoothly, you must first execute the
SET autocommit = on;command to enable automatic transaction commit. - If the data from the source table, after being processed by the target table's partitioning rules, does not hit the specified target table partition, an error indicating the partition does not exist will be reported.
- Target tables with auto-increment columns are not currently supported.
- Global indexes or foreign keys on the target table are not currently supported.
- External tables as target tables are not currently supported.
INSERT OVERWRITE SELECT examples
Example 1
Create two test tables:
source_tbl1as the data source andtarget_tbl1as 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_tbl1table.INSERT INTO source_tbl1 VALUES (1, 'A1', 30),(2, 'B2', 25),(3, 'C3', 22);Insert sample data into the
target_tbl1table.INSERT INTO target_tbl1 VALUES (4, 'D4', 35),(5, 'E5', 28);Query the data in the
target_tbl1table.SELECT * FROM target_tbl1;The query result is as follows:
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 4 | D4 | 35 | | 5 | E5 | 28 | +------+------+------+ 2 rows in setUse the
INSERT OVERWRITE SELECTstatement to filter data fromsource_tbl1wherecol3is greater than 25, and insert this data intotarget_tbl1, replacing its existing content.INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;View the data in the
target_tbl1table after the data was replaced.SELECT * FROM target_tbl1;The query result is as follows:
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | A1 | 30 | +------+------+------+ 1 row in set
Example 2
Example of using INSERT OVERWRITE PARTITION SELECT:
Create the partitioned table
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 affectedCreate the partitioned table
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 affectedInsert several rows of data into each table.
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: 0Query the data in the p3 partition of 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)Execute the following SQL statement to insert the data from partition p3 of table
t1_ins_overwrtinto partition p3 of tablet2_ins_overwrt.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: 0Query partition p3 of table
t2_ins_overwrtand find that the 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)
Example 3
Create a partitioned table named
tbl1.obclient [test]> CREATE TABLE tbl1(col1 INT,col2 INT) PARTITION BY RANGE COLUMNS(col1) SUBPARTITION BY HASH(col2) SUBPARTITIONS 3 ( PARTITION p0 VALUES LESS THAN(10), PARTITION p1 VALUES LESS THAN(20)); Query OK, 0 rows affectedInsert a row of data into table
tbl1.obclient [test]> INSERT INTO tbl1 (col1, col2) VALUES (2, 3); INSERT INTO tbl1 (col1, col2) VALUES (15, 2);Query whether data has been inserted into partition p0 of table
tbl1.obclient [test]> SELECT * FROM tbl1 PARTITION (p0);The query result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 2 | 3 | +------+------+obclient [test]> SELECT * FROM tbl1 PARTITION (p1);The query result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 15 | 2 | +------+------+Create a partitioned table named
tbl2.obclient [test]> CREATE TABLE tbl2(col1 INT,col2 INT) PARTITION BY RANGE COLUMNS(col1) SUBPARTITION BY HASH(col2) SUBPARTITIONS 3 ( PARTITION p0 VALUES LESS THAN(10), PARTITION p1 VALUES LESS THAN(20)); Query OK, 0 rows affectedInsert data into table
tbl2.obclient [test]> INSERT INTO tbl2 (col1, col2) VALUES (0, 22); INSERT INTO tbl2 (col1, col2) VALUES (18, 6);Query whether data has been inserted into partitions
p0andp1of tabletbl2.obclient [test]> SELECT * FROM tbl2 PARTITION (p0);The query result is as follows:
+------+------+ | c1 | c2 | +------+------+ | 0 | 22 | +------+------+obclient [test]> SELECT * FROM tbl2 PARTITION (p1);The query result is as follows:
+------+------+ | c1 | c2 | +------+------+ | 18 | 6 | +------+------+Use
insert overwriteto overwrite the data in tabletbl1into the specified partitionp1of tabletbl2.obclient [test]> insert overwrite tbl2 partition (p1) select * from tbl1 partition (p1);Verify whether the data in partition p1 of table
tbl2has been overwritten.obclient [test]> SELECT * FROM tbl2 PARTITION (p1);The query result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 15 | 2 | +------+------+The result shows that the data in partition p1 of table
tbl2has been overwritten.
Insert data using the REPLACE INTO statement
In addition to the INSERT statement, when a table has no data records, or when the table has data records but there is no primary key or unique key conflict, you can also use the REPLACE INTO statement instead of the INSERT statement to insert data. For detailed syntax and description of the REPLACE INTO statement, see REPLACE.
Examples:
After creating the
t_replacetable, use theREPLACE INTOstatement to insert data.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 setIn the table
t_replacewith data records, use theREPLACE INTOstatement to insert data.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