The INSERT statement is used to insert a new row into a table.
The syntax for the simplest INSERT statement:
INSERT INTO table_name (list_of_columns) VALUES (list_of_values);
Where, list_of_columns specifies the table_name column of the table. list_of_values specifies a list of values corresponding to each column that you specified in list_of_columns. Therefore, 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 command-line (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 (0.01 sec)
In an INSERT statement, you do not need to know the values in every column of a table, but you need to know the values in the columns that have a NOT NULL constraint. For a NOT NULL column that has a default value, you do not need to specify the values. For a NULL column, you do not need to specify values either because OceanBase Database will insert 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.
Example: Using an INSERT statement when you know all column information
The following example creates a table where a column has default values and uses an SQL statement to insert two records. All fields have values.
obclient> 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 (0.07 sec)
obclient> INSERT INTO t_insert(id, name, value, gmt_create)
values(1,'CN',10001, sysdate);
Query OK, 1 row affected (0.01 sec)
Example: Using an INSERT statement when you do not know all column information
An SQL statement is used in the following example to insert two records, but the gmt_create field is not provided. The two records are inserted by using an INSERT statement that has multiple VALUES clauses.
obclient> INSERT INTO t_insert(id, name, value)
VALUES (2,'US', 10002) ,(3,'EN', 10003);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Example: Using 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.
Example: Using a SELECT...FROM statement as an INSERT statement that has multiple VALUES clauses
To back up all or part of 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 (0.17 sec)
obclient> insert into ware_bak select * from ware;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0