Create new rows in a table.

 

Syntax

 

<insert statement> ::=

 

 

 

INSERT INTO [ schema-name. ] table-name <insert columns and source>

 

<insert columns and source> ::=

 

|

|

 

<from subquery>

<from constructor>

DEFAULT VALUES

 

<from subquery> ::=

 

 

 

[ ( column-name [ { , column-name }... ] ) ] <query expression>

 

<from constructor> ::=

 

 

 

[ ( column-name [ { , column-name }... ] ) ] <table value constructor>

 

<table value constructor> ::=

 

 

 

VALUES ( <row value expression> [ { , <row value expression> }... ] )

 

 

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.



 

The values assigned in the VALUES clause can be any valid value expression, including value functions, user-defined functions, scalar subqueries, case expressions, DEFAULT and NULL.

 

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

-

-

Core SQL

Feature F222 "INSERT statement: DEFAULT VALUES clause"