insert

Insert a row or rows into a table

Syntax

insert_stmt:
          INSERT INTO [database_name.]table_name [( column_name [, column_name]... )] data_source

data_source:
          VALUES insert_value_list
     |    [FROM] select_stmt

insert_value_list:
          [value_expr] [, [value_expr]]...
     |    ( [value_expr] [, [value_expr]]... )

Description

The INSERT statement is used to insert new rows into table table_name in database database_name. If database_name is not specified, then the first table named table_name found in the set of opened databases starting from the most recently opened will be used.

If a column_name list is not specified, the values must be listed in the same order as the columns have been declared in the CREATE TABLE statement for table_name.

Two forms of the INSERT statement are available. Use of the VALUES clause specifies the values of the columns of the single row to be inserted into table_name. If a select_stmt is specified, it must return the number of result columns that match either the specified column_name list or the columns in the order declared in the table. The data type of each expression result in the VALUES list or the SELECT statement result columns must be commensurate with the corresponding table column's data type. An insert_expr can be empty in which case the column’s default value will be stored or, if no default was specified, the column will be null.

Column names can be referenced in a VALUE expression but only one column reference in a value_expr is allowed and the referenced column's value_expr itself cannot contain a column reference.

The arg_name value_operand only applies if the INSERT statement is part of a CREATE PROCEDURE/FUNCTION statement.

Values for columns declared as ROWID PRIMARY should not be specified as these values are automatically generated by the SQL system. Just leave an empty entry in the INSERT VALUES list in the location where that column value belongs as shown in the example below.

create table acctmgr(
    mgrid       rowid primary key,
    name        char(24),
    hire_date   date,
    commission  double
);

insert into acctmgr values ,"Sam Smith",@"2010-04-01",0.05;

The initial comma in the values list is left for the unspecified rowid value. It is possible to explicitly specify a rowid primary key value. However, this is designed to be used with the EXPORT and IMPORT statements to preserve rowid primary and foreign key values.

Similarly, values for columns declared as guid default auto primary key should not be specified as these values are also automatically generated by the SQL system. As with rowid primary key, leave an empty entry in the insert values list in the location where that column value belongs.

Example

insert into author values "BarrieJ", "Barrie, J. M. (James Matthew)", "M", 1860, 1937,
    "Scottish author/dramatist, best remembered today as the creator of Peter Pan.";
insert into book values "descartes01", "DescartesR", "Principia philosophiae", 
 	"12 p.l., 310 p. illus., diagrs. 21 cm.", "Amstelodami, apud Ludovicum Elzevirium", 
 	1644, "B1860  1644", date "2010-09-22", null, 1.20*cost, 12750.0;
 	...
insert into se_tfs.nsforg select * from ne_tfs.nsforg;
	...
insert into person(name) values "Unknown, Manager";