|
Create new rows in a table.
Syntax
<insert statement> ::=
<insert columns and source> ::=
<from subquery> ::=
<from constructor> ::=
Usage
The INSERT statement is used to add new rows to the table identified by table-name.
Notes
§
|
The current database is implicit if schema-name is not specified.
|
§
|
There are two main variations of the INSERT statement, the first is using a query expression to add rows based on data from existing tables in the database, and the second is using a table value constructor to specify a single row or a comma-separated list of row values.
|
§
|
The DEFAULT VALUES clause is a third option that can be used to insert a new row with default values in every column.
|
§
|
The optional comma-separated list of insert columns is particularly useful when inserting rows into a wide table and only a few columns will have initial data. Columns that are not specified in the insert column list will get the default value if specified in the table definition, otherwise the null value.
|
§
|
The number of values in the inserting rows, whether retrieved by a query expression or specified in the VALUES clause, shall match the number of insert columns or, if the insert column list is omitted, the degree of the target table.
|
§
|
Parameters can be used to represent values supplied by the application.
|
Tip: Using multiple row values in the VALUES clause is more efficient than executing a sequence of single row INSERT statements.
Examples
1)
|
The following example inserts a new row into the students table, only assigning values to some of the columns:
|
INSERT INTO students
( studentID, lastName, firstName , address, state, zip, gender)
VALUES ( 211, 'Smith', 'Joanne', NULL, 'QL', 45678, 'F' )
2)
|
The following example shows a generic INSERT statement for the students table using parameters to assign column values:
|
INSERT INTO students
VALUES (
:studentID,
:firstName || ' ' || :lastName,
:lastName,
:firstName,
:address,
:city,
:state,
:zip,
:gender
)
3)
|
The following example uses a subquery to insert all the rows of the students table into the ##students table:
|
DELETE FROM ##students;
INSERT INTO ##students
SELECT * FROM students
4)
|
The following example inserts two new rows into the documents table:
|
INSERT into documents ( documentID, title )
VALUES ( NEWGUID, 'NexusDB main features' ), ( NEWGUID, 'NexusDB special features' )
Conformance
SQL:2003 standard
NexusDB extensions
|
-
-
-
|
Core SQL
Feature F222 "INSERT statement: DEFAULT VALUES clause"
Support for the ORDER BY clause in <from subquery>
|
|
