INSERT statement Format 1 (Insert row)
Function
Inserts rows into a table in units of columns. The statement can be used to insert one row by directly specifying values. In addition, this command can also insert one or more rows by using a query expression body.
Privileges (Format 1)
A user who has the INSERT privilege for a table can insert rows into that table.
However, if a query is specified in the INSERT statement, the user needs the SELECT privilege for the table for which the query is specified.
Format 1: Inserting rows into a table on a column-by-column basis
INSERT INTO [[RD-node-name.]authorization-identifier.]
table-identifier
{[(column-name [, column-name]...)]
{VALUES (insertion-value [, insertion-value]...)
|query-expression-body}
|DEFAULT VALUES}
[WITH ROLLBACK]
query-expression-body:: = {query-specification
|(query-expression-body)
|query-expression-body {UNION|EXCEPT}[ALL]
query-specification|(query-expression-body)}}
query-specification :: = SELECT[{ALL|DISTINCT}]{selection-expression[, selection-expression]...|*} table-expression
table-expression :: = FROM table-reference[, table-reference]...
[WHERE search-condition]
[GROUP BY value-expression[, value-expression]...]
[HAVING search-condition]
Operands
Specifies the names of the columns into which data is to be inserted.
The following rules apply to column names:
Specifies the query specification body that fetches the data to be inserted.
For details about query expression bodies, see 2.2 Query expressions.
For details about query specifications, see 2.3 Query specification.
If the column into which the fetched data is to be inserted is a repetition column, specify an unsubscripted repetition column in the selection expression in the query specification associated with that column.
For table expressions, see 2.5 Table expressions. For table referencing, see 2.6 Table reference. For search conditions, see 2.7 Search conditions.
Inserts a predefined value for all columns in the row to be inserted.
DEFAULT VALUES has the same meaning as the specification of the following format:
VALUES(DEFAULT,DEFAULT,...)
The number of DEFAULT specifications above is equal to the number of columns in the table that is subject to the insertion operation.
Specifies that if the table that is subject to the insertion operation is being used by another user, the transaction issued by that user is to be cancelled and invalidated.
When the WITH ROLLBACK option is omitted and the table subject to the insertion operation is being used by another user, the current user must wait until the transaction issued by that user is completed.
Common rules
Rules on referential constraints
Examples
INSERT INTO STOCK
VALUES(:XPCODE,:XPNAME,:XCOLOR,
:XPRICE,:XSQTY)
INSERT INTO STOCK
(PCODE,PNAME,SQTY)
VALUES(:XPCODE,:XPNAME,:XSQTY)
INSERT INTO STOCK
SELECT * FROM STOCK2
INSERT INTO STOCK (PCODE,PNAME,COLOR)
VALUES('612S',N'PANTS',N'WHITE')
INSERT INTO ORDERS
VALUES('02561','TT001','302S',50,
CURRENT_DATE,CURRENT TIME)