skip to main content
SQL statements : DML statements : INSERT
 

INSERT

This statement adds one or more rows to a table.
To insert rows into a table, the IP must mark support for INSERT operation.

Syntax 

<insert-stmt> ::= INSERT INTO <table_name> [<column-list>]
<query-or-values>
<column-list> ::= <column-name> [,<column-name>]…
 
<query-or-values> ::= <query-specification>
| VALUES (insert-value-row)
| VALUES (insert-value-row-list)
<insert-value-row-list> ::= <insert-value-row> [,<insert-value-row>]…
 
<insert-value-row> ::= <insert-value> [,<insert-value>]…
 
<insert-value> ::= <literal>| NULL| ?| <scalar-function>

Examples

Example A
The following statement inserts a row into the dept table:
INSERT INTO dept
VALUES (50, 'PRODUCTION', 'SAN FRANCISCO', 5);
Example B
The following statement inserts a row with six columns into the emp table. One is assigned NULL and another is assigned a number in scientific notation:
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
Example C
The following statement inserts a set of rows into the emp table:
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (
(7000, 'JANE', 'CLERK', 1.2E3, NULL, 40),
(7010, 'KATE', 'PRESIDENT', 3510.25, 12.50, 40),
(7020, 'MARK', 'MANAGER', 1500.00, 10.75, 40));
Example D
The following examples demonstrate how to use expressions in the VALUE clause of an INSERT statement:
• INSERT INTO emp (empno, empno, job, hiredate, sal, comm, deptno) VALUES(empno, 'name'+'115', 'title3',{ts '1997-03-12 08:09:54'}, 3000.00, 1000.50, 2);
• INSERT INTO emp (empno, empno, job, hiredate, sal, comm, deptno) VALUES(110+1, 'name10', 'titile3',{ts '1997-03-12 08:09:54'}, 3000.00, 1000.50, 2);