Insert Values
The insert values statement is used to insert a new row into a table. Its syntax is as shown in the box below.
insert_values_stmt:
INSERT INTO [database_name.]table_name [(column_name [, column_name]...)]
VALUES insert_value_listinsert_value_list:
[value_expr] [, [value_expr]]...
| ( [value_expr] [, [value_expr]]... )
The INSERT VALUES statement is used to insert a single row into the table table_name. If a column_name list is specified it must include every column which requires that a value be specified (a primary key column or one which does not have a default value but does have a NOT NULL declared). For each column, there must be a value specified in the same corresponding position in the values list. If no column_name list is specified then there must be a value listed for each column declared in the table in the order in which the columns were declared in the CREATE TABLE statement for table_name.
database_name
Values for columns declared as ROWID PRIMARY KEY 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
);
create table patron(
patid char(3) primary key,
name char(30),
city char(17),
state char(2),
mgrid rowid references acctmgr
);
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.
The values specified in the values list will usually simply be a constant of a data type that is compatible with the data type of its corresponding column. However, simple expressions can be used and besides constant values can include a reference to another column value in the list, parameter marker references (designated by a "?") or, if the INSERT statement is contained within a CREATE PROCEDURE statement, procedure argument or variable names. Expressions can also include calls to the built-in SQL functions or to a user-defined function. Use of functions will be described in detail in the Changing and Deleting Data in a Database section and in the User-Defined Functions (UDFs) in SQL section. If a values list entry includes a column_name it must reference another column in the table and the values list entry for that column cannot itself include a column_name reference.
Primary key values need to be stored in the rows of the referencing (foreign key) tables. After executing an INSERT into the primary key table some method needs to be provided where the application can discover the rowid of the inserted row so that it can be included in the VALUES clause of the INSERT statement(s) which insert those referencing rows. The built-in SQL function last_insert_id can be used to do this...
Built-in Function last_insert_id()
This function behaves similarly to the MySQL function of the same name. If no table_name is specified, it returns the rowid of the most recently inserted row whether or not the associated table has a rowid primary key. If the table_name is specified then the function returns the most recently inserted row for the specified table. The function returns an error if any of the following conditions exist:
- No rows have been inserted into the specified table (or any table if table_name is not specified),
- The most recently executed insert statement returned an error,
- The transaction in which the most recently executed INSERT was included was rolled back.
Referring to the example acctmgr and patron table declarations given in the last section the following example shows how last_insert_id can be used to store the correct rowid foreign key value into the member_table.
insert into acctmgr values ,"Sam Smith",@"2010-04-01",0.05;
insert into patron values "IBM", "IBM Corp", "Boca Raton","FL",last_insert_id();
If the patron table contained two rowid foreign key columns referencing different tables then after having inserted each of the two primary key rows the INSERT VALUES statement for the referencing table would include two last_insert_id function calls in which the specific table names would be passed in as the argument.
Here are some example INSERT statements:
start transaction;
insert into author values "DescartesR", "Descartes, Rene", "M", 1596, 1650,
"French philosopher, mathematician, physicist, and writer";
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 related_name values "descartes01", "Lessing J. Rosenwald Collection";
insert into related_name values "descartes01", "John Davis Batchelder Collection";
insert into note(noteid, bookid) values nextnote(), "descartes01";
insert into note_line values thisnote(), "Title vignette: device of Louis Elzevir.";
insert into note_line values thisnote(), "Last preliminary leaf (sig. b[4]) blank.";
commit;
There are several things to notice from this example. The first is the presence of the start transaction and commit statements that enclose the seven insert statements. As was discussed in the last section, since all of the data being inserted into the database is interrelated, by placing it inside a single transaction unit, the system guarantees that either all of the data will be reliability stored in the database or, in the event of a system failure during the transaction, none of it will. If each insert statement was individually committed then, should a failure occur, some of the data would be missing. Therefore, it is always best to enclose all related database modification statements (i.e., INSERT, UPDATE, and DELETE) in a transaction.
The value associated with the price column in the second INSERT statement (i.e., the next to last entry in the values list) is an expression that references the cost column (the last entry in the list). In this example, the asking price for the book is calculated as a 20% markup over the cost of the book.