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, make sure that:

• You have logged on to an Oracle tenant of OceanBase Database. For more information, see Connect to the OceanBase Database.

• You have the INSERT privilege on the target table. For more information about how to view the privileges of the current user, see View user privileges. If you do not have the required privileges, request the administrator to grant the privileges. For more information, see Modify user privileges.

Use an INSERT statement to insert data

Use the INSERT statement to insert data into a table.

Syntax:

INSERT INTO table_name (list_of_columns) VALUES (list_of_values);

Considerations

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

  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:

    • 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 the column has no default value, 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, we recommend that you learn about the constraints defined on columns of the table 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 in a batch](# Insert multiple rows of data in a batch).

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 by using multiple single-row insertion statements.

A default value has been specified for the gmt_create column. Therefore, you do not need 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 in a batch

To insert multiple records, you can also use an INSERT statement that contains multiple VALUES. The execution of a single multi-row insertion statement is faster than that of multiple single-row insertion statements.

The operation in example 1 can be completed by using the following statement.

Example 2: Insert multiple rows of data in a batch.

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 MERGE INO 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 destination table, you can also use the MERGE INTO statement to insert the records of the source table into the destination table. For more information, see Replace data.

Use DBLink to insert data

The current OceanBase Database version allows you to use DBLink to write data into OceanBase Database in Oracle mode and Oracle databases.

Example: Use DBLink to insert the (11,11) row into the t1 table of a remote database.

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

More information

• Update data

• Delete data

• Replace data

• Use a DBLink to modify data in a remote database