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,
    gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
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, current_timestamp);
  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.

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

• If you use INSERT IGNORE INTO to avoid constraint conflicts, the IGNORE keyword can be used to ignore the impact of an insert failure caused by a constraint conflict.

  obclient> INSERT IGNORE INTO t_insert(id, name, value)
      VALUES (3,'UK', 10003),(4, 'JP', 10004);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 1970-01-01 16:05:45 |
  |  2 | US   | 10002 | 1970-01-01 16:05:54 |
  |  3 | EN   | 10003 | 1970-01-01 16:05:54 |
  |  4 | JP   | 10004 | 1970-01-01 16:06:08 |
  +----+------+-------+---------------------+
  4 rows in set
  

• 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. In the following example, the conflicting content is updated.

  Note

  • With ON DUPLICATE KEY UPDATE specified, when the primary key or unique key to be inserted is duplicated with an existing one, the value to be inserted is replaced with the specified value.

  • With ON DUPLICATE KEY UPDATE unspecified, when the primary key or unique key is duplicated with an existing one, an insert error is reported.

  obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004) ON DUPLICATE KEY UPDATE name=VALUES(name);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 1970-01-01 16:05:45 |
  |  2 | US   | 10002 | 1970-01-01 16:05:54 |
  |  3 | UK   | 10003 | 1970-01-01 16:05:54 |
  |  4 | JP   | 10004 | 1970-01-01 16:06:08 |
  +----+------+-------+---------------------+
  4 rows in set
  

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