AntDB-Oracle Compatibility Developer’s Manual P3–18
Published: 2023-09-04 05:23:00

#### INSERT

**Name**

INSERT -- create a new record in the table.

**Summary**

INSERT INTO table \[ ( column \[, ...] ) ]

{ VALUES ( { expression \| DEFAULT } \[, ...] )

\[ RETURNING return\_expression \[, ...]

{ INTO { record \| variable \[, ...] }

\| BULK COLLECT INTO collection \[, ...] } ]

\| query }

**Description**

The INSERT command is used to insert new rows into a table. We can either insert one row at a time or insert multiple rows based on the results of a query.

The columns in the destination list can be listed in any order. For columns that do not appear in the destination list, the INSERT command inserts a default value, which can either be specified when the column is declared or can be null.

If the expression corresponding to each column is not of the correct data type, then an automatic type conversion is attempted.

We can specify RETURNING INTO { record \| variable \[, ...] only if the INSERT command is used in an SPL program and only if the VALUES clause is used. } clause.

The RETURNING BULK COLLECT INTO collection \[, ...] clause can be used only when the INSERT command is used in the SPL program. clause. If multiple collections are specified as the target of the BULK COLLECT INTO clause, then each collection must consist of a separate scalar field. The return\_expression computed for each inserted record becomes a member of the collection, and the position of this collection starts with the first member. Any record that already exists in the set will be deleted. If the result set is empty, then the collection will also be empty.

In order to insert data into a table, you must have INSERT permission on the table. If you are inserting query results from a query clause into a table, then you also need to have SELECT privileges on the table used for the query.

**argument**

table

The name of an existinf table (which can be referenced in a schema-qualified manner）.

column

The name of the column in the table.

expression

Express of value distributed to column.

DEFAULT

Use the default value to populate the column.

query

Provides the SELECT statement into which the row is to be inserted. See the SELECT command for the specific syntax.

return\_expression

An expression containing one or more columns of a table. If a column of the table is specified in return\_expression, then the value used for the replacement column when calculating return\_expression is determined by the following.

If a value is assigned to the column specified in return\_expression in the INSERT command, then the assigned value is used when calculating return\_expression.

If no value is assigned to the column specified in the return\_expression in the INSERT command and there is no default value in the table column definition, then the null value is used when calculating the return\_expression.

If no value is assigned to the column specified in the return\_expression in the INSERT command, but there is a default value in the table column definition, then the default value is used when calculating the return\_expression.

record

The field value of the record is the calculated return\_expression. The first computed return\_expression is assigned to the first field in the record, and the second computed return\_expression is assigned to the second field in the record in this order. The number of fields in the record must be the same as the number of expressions. The types of the fields and the expressions assigned to them must be type-compatible.

variable

The value of the variable is the computed return\_expression. If multiple return\_expressions and variables are specified, they are assigned in an order such that the first return\_expression is assigned to the first variable and the second return\_expression is assigned to the second variable. The number of variables following the keyword INTO must be the same as the number of expressions following the RETURNING clause, and the variables and the expressions assigned to them must be type-compatible.

collection

The members of a collection are created by a computed return\_expression. Here i.e. it can be a single collection, a collection containing separate fields or a collection of record types, or there can be multiple collections, each consisting of a single field. The number and order of occurrence of the returned expressions must be consistent with all the fields in the specified collection. The type of each return\_expression corresponding to a field in the collection must be compatible.

**Example**

Insert a record into the emp table.

```
INSERT INTO emp VALUES (8021,'JOHN','SALESMAN',7698,'22-FEB-07',1250,500,30); 
```

In the second example, there is no column comm in the target list, so the null value will be inserted into this column as the default value:

```
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno) VALUES (8022,'PETERS','CLERK',7698,'03-DEC-06',950,30); 
```

In the third example, no value is specified on the columns hiredate and comm, but rather the DEFAULT clause is used.

```
INSERT INTO emp VALUES (8023,'FORD','ANALYST',7566,NULL,3000,NULL,20);
```

In this example to create a table for the department name, the value of the column dname from the table dept will be inserted into the table deptname.

```
CREATE TABLE deptnames (
deptname	VARCHAR2(14)
);
INSERT INTO deptnames SELECT dname FROM dept;
```