About the INSERT statement

You can use the INSERT statement to insert rows into a table. This topic describes how to use the INSERT statement.

INSERT statement

Syntax:

INSERT INTO table_name (list_of_columns) VALUES (list_of_values);

Before you insert a row into a table, you need to know the information of all columns in the table, including the column types, valid values, and whether the columns can contain null values. In the CLI environment of OceanBase Client (OBClient), you can directly use the DESC command to view the column attributes. Example:

obclient> DESC ordl;
+----------------+-------------+------+-----+---------+-------+
| FIELD          | TYPE        | NULL | KEY | DEFAULT | EXTRA |
+----------------+-------------+------+-----+---------+-------+
| OL_W_ID        | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
| OL_D_ID        | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
| OL_O_ID        | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
| OL_NUMBER      | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
| OL_DELIVERY_D  | DATE        | YES  | NULL | NULL    | NULL  |
| OL_AMOUNT      | NUMBER(6,2) | YES  | NULL | NULL    | NULL  |
| OL_I_ID        | NUMBER(38)  | YES  | NULL | NULL    | NULL  |
| OL_SUPPLY_W_ID | NUMBER(38)  | YES  | NULL | NULL    | NULL  |
| OL_QUANTITY    | NUMBER(38)  | YES  | NULL | NULL    | NULL  |
| OL_DIST_INFO   | CHAR(24)    | YES  | NULL | NULL    | NULL  |
+----------------+-------------+------+-----+---------+-------+
10 rows in set

To use the INSERT statement, you need to know the attributes of all columns.

• For a NOT NULL column:

  • If a default value has been specified for the column, you can choose not to specify a value for the column, and OceanBase Database 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.

• For a NULL column, if you do not specify a value for this column, OceanBase Database then inserts a null value into the column.

To insert a multi-condition record into a table, you can use multiple INSERT statements or a single INSERT statement that has multiple VALUES clauses.

Examples

Create a table in which the gmt_create column has a default value.

obclient> CREATE TABLE t_insert(
     id number NOT NULL PRIMARY KEY,
     name varchar(10) NOT NULL,
     value number NOT NULL,
     gmt_create date NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected

Use an INSERT statement to insert data

• Information about all columns known

  Use the following SQL statement to insert a record in which all columns have values:

  obclient> INSERT INTO t_insert(id, name, value, gmt_create)
      values(1,'CN',10001, sysdate);
  Query OK, 1 row affected
  

• Information about some columns unknown

  Use the following INSERT statement with multiple VALUES clauses to insert two records.

  Note

  No value is specified for the gmt_create column, but the column has a default value. Therefore, data can be successfully inserted.

  obclient> INSERT INTO t_insert(id, name, value)
       VALUES (2,'US', 10002) ,(3,'EN', 10003);
  Query OK, 2 rows affected
  

Use an INSERT statement to insert values that violate the UNIQUE constraint

When you insert identical records into a table that has a UNIQUE constraint, the database returns an error, indicating key 'xxx' violated.

obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
ORA-00001: unique constraint '3' for key 'PRIMARY' violated

You can use the MERGE INTO statement to avoid this error. For more information, see About the MERGE statement.

Use a query statement as a VALUES clause in an INSERT statement

To back up some or all of the records in a table, you can use the INSERT INTO … SELECT … FROM statement.

obclient> CREATE TABLE ware_bak(
    w_id int,
    w_ytd decimal(12,2),
    w_tax decimal(4,4),
    w_name varchar(10),
    w_street_1 varchar(20),
    w_street_2 varchar(20),
    w_city varchar(20),
    w_state char(2),
    w_zip char(9),
    primary key(w_id),
);
Query OK, 0 rows affected

obclient> INSERT INTO ware_bak SELECT * FROM ware;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0