Skip to main content

Insert data

After a table is created, you can use the INSERT statement or other statements to insert row records into the table. This topic describes the usage and provides examples.

Prepare to insert data​

Before you insert data, make sure that the following conditions are met:

  • You have connected to seekdb. For more information, see Connection methods.

  • You have the INSERT privilege on the table to be operated on. For information about how to view the privileges of the current user, see View user privileges. If you do not have the privilege, contact the administrator to grant the privilege. For information about how to directly grant privileges, see Directly grant privileges.

Insert data by using the INSERT INTO statement​

We recommend that you use 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);
ParameterRequiredDescription
table_nameYesThe name of the table into which data is to be inserted.
(list_of_columns)NoThe names of the columns into which data is to be inserted.
(list_of_values)YesThe values corresponding to the columns in list_of_columns. The values must be in the same order as the columns in list_of_columns.

Suggestions​

  • Before you insert data, we recommend that you learn about the columns in the table, including their types, valid values, and whether they allow NULL values.

    You can run the DESC statement to query the column information.

    DESC test;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | col1  | int(11) | NO   |     | NULL    |       |
    | col2  | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    2 rows in set

    • If the column attribute is NOT NULL:

      • If the column has a default value, you can omit the value of the column when you insert data. The system automatically inserts the default value into the column.

      • If the column does not have a default value, you must specify the value of the column when you insert data.

    • If the column attribute is NULL, you can omit the value of the column when you insert data. The system automatically inserts a NULL value into the column.

  • Before you insert data, we recommend that you learn about the constraints defined on the columns in the table to avoid errors.

    You can run the DESC statement to query the NOT NULL and PRIMARY KEY constraints and the UNIQUE constraints. You can query the information_schema.TABLE_CONSTRAINTS view to query the FOREIGN KEY and CHECK constraints.

Insert a single row​

You can run the INSERT statement to insert a single row. To insert multiple rows, you can run multiple single-row insert statements. To batch insert multiple rows, see [Batch insert multiple rows](#Batch insert multiple rows).

Assume that the information of the table into which data is to be inserted is as follows:

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 empty. The id column is the primary key and must be unique. The gmt_create column has a default value.

Example 1: Insert multiple rows by using multiple single-row insert statements.

Since the gmt_create column has a default value, you can omit the value of this column when you insert data.

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

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 the value of this column when you insert data. The statement is as follows:

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

Batch insert multiple rows​

You can also run a single INSERT statement that contains multiple VALUES clauses to batch insert multiple rows. A single multi-row insert statement is faster than multiple single-row insert statements.

The operation in Example 1 can also be performed by using the following statement:

Example 2: Batch insert multiple rows.

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

In addition, when you need to back up table data or copy all records from one table to another, you can use the INSERT INTO ... SELECT ... FROM statement to batch insert data. In this statement, the SELECT clause serves as the values clause of the INSERT statement.

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

SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2025-12-22 21:41:28 |
|  2 | US   | 10002 | 2025-12-22 21:41:32 |
|  3 | EN   | 10003 | 2025-12-22 21:41:43 |
+----+------+-------+---------------------+
3 rows in set

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 

INSERT INTO t_insert_bak SELECT * FROM t_insert;
Query OK, 3 rows affected

SELECT * FROM t_insert_bak;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2025-12-22 21:41:28 |
|  2 | US   | 10002 | 2025-12-22 21:41:32 |
|  3 | EN   | 10003 | 2025-12-22 21:41:43 |
+----+------+-------+---------------------+
3 rows in set

Avoid uniqueness constraint conflicts​

When a uniqueness constraint is defined on a table, the database returns an error if you insert duplicate records. The error message is as follows:

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 statement or the INSERT INTO ON DUPLICATE KEY UPDATE statement to avoid this error.

Example:

  • When you use the INSERT IGNORE INTO statement to avoid constraint conflicts, the IGNORE keyword ignores the impact of the INSERT failure caused by constraint conflicts.

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

    SELECT * FROM t_insert;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   | 10001 | 2025-12-22 21:41:28 |
    |  2 | US   | 10002 | 2025-12-22 21:41:32 |
    |  3 | EN   | 10003 | 2025-12-22 21:41:43 |
    |  4 | JP   | 10004 | 2025-12-22 21:43:28 |
    +----+------+-------+---------------------+
    4 rows in set

    In this example, the INSERT IGNORE INTO statement is used. The row of data (3,'UK', 10003) fails to be inserted, but no error is returned.

  • When you use the INSERT INTO ON DUPLICATE KEY UPDATE statement to avoid constraint conflicts, you can specify the subsequent processing for duplicate primary keys or unique keys.

    info

    • Specify ON DUPLICATE KEY UPDATE column_name = expr: When the primary key or unique key to be inserted is duplicate, you can use the column_name = expr assignment statement to update the data of the conflicting row in the table. The column_name = expr assignment statement can assign values to one or more columns in the conflicting row. When you assign values to multiple columns, separate the columns with commas.

    • Do not specify ON DUPLICATE KEY UPDATE column_name = expr: When the primary key or unique key to be inserted is duplicate, an error is returned when you insert data.

    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 

    SELECT * FROM t_insert;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   | 10001 | 2025-12-22 21:41:28 |
    |  2 | US   | 10002 | 2025-12-22 21:41:32 |
    |  3 | UK   | 10003 | 2025-12-22 21:41:43 |
    |  4 | JP   | 10004 | 2025-12-22 21:43:28 |
    |  5 | CN   | 10005 | 2025-12-22 21:46:27 |
    +----+------+-------+---------------------+
    5 rows in set

    In this example, ON DUPLICATE KEY UPDATE name = VALUES(name) indicates that when the data to be inserted has a duplicate primary key value, the name value of the conflicting row (3,'EN', 10003) in the table is updated to the name value of the data to be inserted. Other rows that do not conflict are inserted normally.

Use the INSERT OVERWRITE SELECT statement to insert data​

The INSERT OVERWRITE SELECT statement is used to replace the existing data in a table or partition with the query result. This means that the data retrieved by the query will overwrite the data in the target table or partition.

The syntax of this statement is as follows:

INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt;
ParameterDescription
PARALLEL(N)Optional. Specifies the degree of parallelism for the overwrite operation. If not specified, the default degree of parallelism is 2.
table_nameSpecifies the name of the table to insert data into.
list_of_columnsSpecifies the columns in the table where data needs to be inserted.
select_stmtSpecifies the SELECT clause. For more information about query statements, see SELECT statement.
partition_nameThe name of the partition to insert data into. When inserting into multiple partitions, separate the partition names with commas (,).

Limitations of INSERT OVERWRITE SELECT​

Limitations of table-level INSERT OVERWRITE SELECT​

  • This statement cannot be used in a multi-row transaction. To ensure smooth operation, execute the SET autocommit = on; command to enable automatic transaction commit.
  • A table lock is placed on the write table, preventing any concurrent DDL operations on the same table. Concurrent DML operations will wait for the table lock to be released, and queries on the table are allowed during this time.
  • The number of columns in the source data and the target table must strictly match for the overwrite operation. Otherwise, an error will occur.
  • The data write operation for this statement uses a full bypass import method. Therefore, it is subject to the limitations of the full bypass import feature. For more information about bypass import, see the Limitations section in Bypass import data by using the INSERT INTO SELECT statement.
  • Specifying a bypass import hint for this statement will result in an error.
  • Due to the limitations of the PDML (Parallel Data Manipulation Language) framework, data cannot be imported in scenarios not supported by PDML. In such cases, the INSERT OVERWRITE SELECT statement will return a "not supported" error. For more information about parallel DML, see Parallel DML.

Partition-level INSERT OVERWRITE SELECT​

Overview

  • The source table can be a partitioned or non-partitioned table, with no specific partition type required.
  • The target table can be a primary or secondary partitioned table.
  • The target table can have local indexes and LOB (Large Object) data.
  • You can specify all or part of the partitions in the target table. If the target table has secondary partitions, you can specify primary and secondary partitions separately or in combination.
  • 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).
  • At the partition level, the insert overwrite operation supports the last-level partition of the target table being a hash or key partition.

Limitations

  • The specified partitions must already exist; otherwise, an error will occur.
  • Since this operation uses full bypass import, you do not need to add the /*+ append */ hint. Otherwise, an error will occur.
  • This statement cannot be used in a multi-row transaction. To ensure smooth operation, execute the SET autocommit = on; command to enable automatic transaction commit.
  • If the data from the source table does not match the specified partitions in the target table after applying the partitioning rules, an error indicating that the partition does not exist will occur.
  • Importing data to a table with auto-increment columns is not supported.
  • Importing data to a table with global indexes or foreign keys is not supported.
  • Importing data to an external table is not supported.

INSERT OVERWRITE SELECT example​

Example 1​

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

    SELECT * FROM target_tbl1;

    The returned 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 insert this data into target_tbl1, replacing its original content.

    INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;

  6. View the data in the target_tbl1 table after the replacement.

    SELECT * FROM target_tbl1;

    The returned result is as follows:

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

Example 2​

Here is an example of using INSERT OVERWRITE PARTITION SELECT:

  1. Create the partitioned table t1_ins_overwrt.

    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 the partitioned table t2_ins_overwrt.

    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 some data into each table.

    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

    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 of each table.

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

    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 insert the data from the p3 partition of the t1_ins_overwrt table into the p3 partition of the t2_ins_overwrt table.

    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 p3 partition of the t2_ins_overwrt table and find that the data has been overwritten.

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

Example 3​

  1. Create the partitioned table tbl1.

       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 a row of data into the tbl1 table.

    INSERT INTO tbl1 (col1, col2) VALUES (2, 3);
    INSERT INTO tbl1 (col1, col2) VALUES (15, 2);

  3. Query the p0 partition of the tbl1 table to check if the data was inserted.

    SELECT * FROM tbl1 PARTITION (p0);

    The query result is as follows:

    +------+------+
    | col1 | col2 |
    +------+------+
    |    2 |    3 |
    +------+------+
    SELECT * FROM tbl1 PARTITION (p1);

    The query result is as follows:

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

  4. Create the partitioned table tbl2.

    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.

    INSERT INTO tbl2 (col1, col2) VALUES (0, 22);
    INSERT INTO tbl2 (col1, col2) VALUES (18, 6);

  6. Query the p0 and p1 partitions of the tbl2 table to check if data was inserted.

    SELECT * FROM tbl2 PARTITION (p0);

    The query result is as follows:

     +------+------+
     | c1   | c2   |
     +------+------+
     |    0 |   22 |
     +------+------+
    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 tbl1 table into the specified partition p1 of the tbl2 table.

    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.

    SELECT * FROM tbl2 PARTITION (p1);

    The query result is as follows:

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

    The result indicates that the data in the p1 partition of the tbl2 table has been overwritten.

Insert data by using the REPLACE INTO statement​

In addition to the INSERT statement, you can also use the REPLACE INTO statement to insert data when there are no data records in the table or when there are data records in the table but no primary key or unique key conflicts. For more information about the REPLACE INTO statement, see REPLACE.

Examples:

  • After the t_replace table is created, use the REPLACE INTO statement to insert data.

    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 

    REPLACE INTO t_replace VALUES(1,'CN',2001, current_timestamp ());
    Query OK, 1 row affected 

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 10:55:53 |
    +----+------+-------+---------------------+
    1 row in set

  • In the t_replace table with data records, use the REPLACE INTO statement to insert data.

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 10:55:53 |
    +----+------+-------+---------------------+
    1 row in set 

    REPLACE INTO t_replace values(2,'US',2002, current_timestamp ());
    Query OK, 1 row affected

    SELECT * FROM t_replace;
    +----+------+-------+---------------------+
    | id | name | value | gmt_create          |
    +----+------+-------+---------------------+
    |  1 | CN   |  2001 | 2025-12-23 10:55:53 |
    |  2 | US   |  2002 | 2025-12-23 10:56:23 |
    +----+------+-------+---------------------+
    2 rows in set

References​

Contents

Edit this page