Insert data

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 a MySQL 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

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);

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 [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 the table for inserting data has the following structure:

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 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 [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 default value, you must specify a value when inserting data. The statement would be as follows:

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 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 [test]> 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 [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;

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 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.
• 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.
• Target tables with auto-increment 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

1. Create two test tables: source_tbl1 as the source table and target_tbl1 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);
   

2. Insert sample data into the source_tbl1 table.

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

3. Insert sample data into the target_tbl1 table.

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

4. 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
   

5. 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;
   

6. 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

The INSERT OVERWRITE PARTITION SELECT statement is used to insert data.

1. 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
   

2. 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
   

3. Insert several records into the two tables.

   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
   

4. Query the data in the p3 partition from the two tables.

   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)
   

5. Execute the following SQL statement to overwrite the p3 partition of the t2_ins_overwrt table with data from the p3 partition of the t1_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
   

6. 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)
   

Example 3

1. 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 affected
   

2. Insert one record into the tbl1 table.

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

3. Query whether data has been inserted into the p0 partition of the tbl1 table.

   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 |
    +------+------+
   

4. 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 affected
   

5. Insert data into the tbl2 table.

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

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

   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 |
    +------+------+
   

7. Use the insert overwrite statement to overwrite the data in the p1 partition of the tbl1 table into the corresponding partition of the tbl2 table.

   obclient [test]> insert overwrite tbl2 partition (p1) select * from tbl1 partition (p1);
   

8. Verify whether the data in the p1 partition of the tbl2 table has 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 the p1 partition of the tbl2 table has been overwritten.

Insert data using the REPLACE INTO statement

In addition to using the INSERT statement, you can use the REPLACE INTO statement to insert data under certain conditions. Specifically, you can use REPLACE INTO when the table is empty or when it contains records but there are no conflicts with primary keys or unique keys.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, which contains 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
  

References

• Update data

• Delete data

• Replace data