Insert data

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 an Oracle-compatible tenant of OceanBase Cloud.

  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. If you do not have the required privileges, contact the administrator to obtain the privileges.

Use the INSERT INTO statement to insert data

Use the INSERT 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);

Considerations

• 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 [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 can choose not to specify a value for this column when you insert data. The system will insert the default value into this column.

    • 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 can choose not to specify a value for this column when you insert data. The system will insert 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 Insert multiple rows of data at a time.

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

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 

The id and name columns of the table cannot be empty. The id column is the primary key column and cannot contain duplicate values because a UNIQUE constraint is defined on this column. 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 has been specified for the gmt_create column. Therefore, you can choose not to specify a value for this column when you insert 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

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 [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 at a time

To insert multiple records, you can also use an INSERT statement that contains multiple VALUES. 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 at a time.

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 the data of a table or copy all records of a table to another table, you can use the INSERT INTO ... SELECT ... FROM statement as the VALUES clause of the INSERT statement for batch insertion.

Example 3: Back up all data of 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 will be rolled back as a whole.

The syntax is as follows:

INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name select_stmt;

Limitations

• 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 will be 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 will wait for the table lock to be released, or until it times out. However, you can still initiate queries on the table during this process.
• In the current version, you can overwrite only all data in the whole table, rather than the data in specific partitions.
• The number of columns in the source data to be inserted by the statement must exactly match that in the target table. Otherwise, an error will be returned.
• The statement writes data in full direct load mode, and is therefore subject to the full direct load feature.
• If you specify a hint for direct load in this statement, an error will be 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 will be returned.

Examples

1. Enable auto commit of transactions.

   SET autocommit = on;
   

2. 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 VARCHAR2(20), col3 INT);
   

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

3. Insert sample data into the source_tbl1 table.

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

4. Insert sample data into the target_tbl1 table.

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

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

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

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

Use the MERGE INTO statement to insert data

In a batch processing task, a large number of INSERT operations may need to be performed on the target table. If the to-be-inserted records in the source table do not exist in the target table, you can also use the MERGE INTO statement to insert the records of the source table into the target table.

Use the INSERT statement to insert data through a DBLink

The current OceanBase Cloud version allows you to write data into OceanBase Cloudin the Oracle compatible mode and into Oracle Database through a DBLink.

Here is an example of inserting a row (11,11) into the t1 table in the remote database through a DBLink:

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 the Oracle compatible mode of OceanBase Cloud, you can use the INSERT, DELETE, UPDATE, and MERGE INTO statements to write data from local tables to remote tables.