|
INSERT statement
An
INSERT statement creates a row or rows and stores them in the named table.
The number of values assigned in an INSERT statement must be the same as the
number of specified or implied columns.
Whenever you insert into a table which has generated columns,
Derby calculates the values
of those columns.
Syntax INSERT INTO table-Name
[ (Simple-column-Name [ , Simple-column-Name]* ) ]
Query [ ORDER BY clause ]
[ result offset clause ]
[ fetch first clause ]
Query can be:
- A SelectExpression
- A single-row or multiple-row VALUES expression
Single-row and multiple-row VALUES expressions
can include the keyword DEFAULT. Specifying DEFAULT for a column inserts the
column's default value into the column. Another way to insert the default
value into the column is to omit the column from the column list and only
insert values into other columns in the table. For more information, see VALUES expression.
The DEFAULT literal is the only value which you can directly
insert into a generated column.
- UNION expressions
When you want insertion to happen with a specific ordering (for example, in
conjunction with auto-generated keys), it can be useful to specify an ORDER BY
clause on the result set to be inserted.
If the Query is a VALUES expression, it cannot contain or be followed by an
ORDER BY, result offset, or fetch first clause. However, if the VALUES
expression does not contain the DEFAULT keyword, the VALUES clause can be put
in a subquery and ordered, as in the following statement:
INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;
For more information about Query, see Query.
Examples INSERT INTO COUNTRIES
VALUES ('Taiwan', 'TW', 'Asia')
-- Insert a new department into the DEPARTMENT table,
-- but do not assign a manager to the new department
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES ('E31', 'ARCHITECTURE', 'E01')
-- Insert two new departments using one statement
-- into the DEPARTMENT table as in the previous example,
-- but do not assign a manager to the new department.
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES ('B11', 'PURCHASING', 'B01'),
('E41', 'DATABASE ADMINISTRATION', 'E01')
-- Create a temporary table MA_EMP_ACT with the
-- same columns as the EMP_ACT table.
-- Load MA_EMP_ACT with the rows from the EMP_ACT
-- table with a project number (PROJNO)
-- starting with the letters 'MA'.
CREATE TABLE MA_EMP_ACT
(
EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DEC(5,2),
EMSTDATE DATE,
EMENDATE DATE
);
INSERT INTO MA_EMP_ACT
SELECT * FROM EMP_ACT
WHERE SUBSTR(PROJNO, 1, 2) = 'MA';
-- Insert the DEFAULT value for the LOCATION column
INSERT INTO DEPARTMENT
VALUES ('E31', 'ARCHITECTURE', '00390', 'E01', DEFAULT)
-- Create an AIRPORTS table and insert into it
-- some of the fields from the CITIES table, with the airport
-- codes sorted alphabetically
CREATE TABLE AIRPORTS (
AIRPORT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
AIRPORT VARCHAR(3),
CITY VARCHAR(24) NOT NULL,
COUNTRY VARCHAR(26) NOT NULL
);
INSERT INTO AIRPORTS (AIRPORT, CITY, COUNTRY)
SELECT AIRPORT, CITY_NAME, COUNTRY FROM CITIES
ORDER BY AIRPORT;
Statement dependency system The INSERT
statement depends on the table being inserted into, all of the conglomerates
(units of storage such as heaps or indexes) for that table, and any other
table named in the statement. Any statement that creates or drops an index or
a constraint for the target table of a prepared INSERT statement invalidates
the prepared INSERT statement.
|
