Adds a new row, or a number of rows, to a table or view.You can
also use the INSERT statement with a cursor to assign values to
the segments in a column of the LIST OF BYTE VARYING data type.
Before you assign values to the segments in a column of the
LIST OF BYTE VARYING data type, you must first assign a value
to one or more other columns in the same row. To do this, use
a positioned insert. A positioned insert is an INSERT statement
that specifies an insert-only table cursor. This type of INSERT
statement sets up the proper row context for subsequent list
cursors to assign values to list segments.
You can specify the name of a static, a dynamic, or an extended
dynamic cursor in a positioned insert. If you specify a static
cursor name, that cursor name must also be specified in a DECLARE
CURSOR statement within the same module. See the DECLARE CURSOR
statement for more information on static, dynamic, and extended
dynamic cursors.
When you use an INSERT statement to assign values to list
segments:
o The current transaction must not be read-only.
o You cannot specify a cursor name that refers to an update
table cursor.
o Your cursor must specify an intermediate table.
o The value that you assign is appended to the end of the list.
1 – Environment
You can use the INSERT statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
INSERT INTO -+-> <table-name> ----+-+---------------------------+-+-+
+-> <view-name> -----+ +-> AS <correlation-name> --+ | |
+-> CURSOR <cursor-name> ---------------------------+ |
+-------------------------------------------------------------------+
+---> DEFAULT VALUES ------------------+->-+---------------------+-+->
+--------------------------------+---+ | +-> returning-clause -+ |
+---> ( -+> <column-name> +-> ) -+ | | |
+-------- , <----+ | | |
+---------------<--------------------+ | |
+-> value-clause ----------------------+ |
+-> select-expr -+--------------------+--->------------------------+
+-> optimize-clause -+
value-clause =
----> VALUES -> ( -++> <parameter> ----------++-> ) --->
|+> <qualified-parameter> +|
|+> value-expr -----------+|
|+> DEFAULT --------------+|
+------- , <---------------+
returning-clause =
-+----------------------------------------++-------------------+->
+-> RETURNING value-expr ----------------++> INTO <parameter> +
+-> PLACEMENT ONLY RETURNING -+-> DBKEY -+
+-> ROWID -+
value-expr =
---+--> numeric-value-expr -----+----->
+--> char-value-expr --------+
+--> date-time-value-expr ---+
+--> interval-value-expr ----+
+--> date-vms-value-expr ----+
+--> DBKEY ------------------+
+--> NULL -------------------+
+--> ROWID ------------------+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
select-merge-clause =
-+-> EXCEPT -+--------------+---+----+-------------------+-->
| +-> DISTINCT --+ | +-> CORRESPONDING --+
+-> INTERSECT -+-------------+-+ +-> NATURAL --------+
| +-> DISTINCT -+ |
+-> MINUS ---------------------+
+-> UNION --+-------------+----+
+-> ALL ------+
+-> DISTINCT -+
3 – Arguments
3.1 – column-name
Specifies a list of names of columns in the table or view. You
can list the columns in any order, but the names must correspond
to those of the table or view.
If you do not include all the column names in the list, SQL
assigns a null value to those not specified, unless columns were:
o Defined with a default
o Based on a domain that has a default
o Defined with the NOT NULL clause in the CREATE TABLE statement
You cannot omit from an INSERT statement the names of columns
defined with the NOT NULL clause. If you do, the statement fails.
Omitting the list of column names altogether is the same as
listing all the columns of the table or view in the same order
as they were defined.
You must omit the list of column names when using the INSERT
statement to assign values to the segments in a column of data
type LIST OF BYTE VARYING. Column names are not valid in this
context.
3.2 – CURSOR cursor name
Keyword required when using cursors. You must use a cursor to
insert values into any row that contains a column of the LIST OF
BYTE VARYING data type.
3.3 – DEFAULT
Forces the named column to assume the default value defined for
that column (or NULL if none is defined).
If the DEFAULT clause is used in an INSERT statement then one of
the following will be applied:
o If a DEFAULT attribute is present for the column then that
value will be applied during INSERT.
o Else if an AUTOMATIC attribute is present for the column then
that value will be applied during INSERT. This can only happen
if the SET FLAGS 'AUTO_OVERRIDE' is used since during normal
processing these columns are read-only.
o Otherwise a NULL will be applied during INSERT.
3.4 – DEFAULT_VALUES
Specifies that every column in the table is assigned the default
value (or NULL, if the column has no default value).
3.5 – INTO parameter
Inserts the value specified to a specified parameter. The INTO
parameter clause is not valid in interactive SQL.
3.6 – INTO
Syntax options:
INTO table-name | INTO view-name
The name of the target table or view to which you want to add a
row. Inserts the value specified to a specified parameter. The
INTO parameter clause is not valid in interactive SQL.
3.7 – limit-to-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a description of the LIMIT TO expression.
3.8 – OPTIMIZE AS query name
The OPTIMIZE AS clause assigns a name to the query. Use the SET
FLAGS 'STRATEGY' to see this name displayed.
3.9 – OPTIMIZE_FOR
The OPTIMIZE FOR clause specifies the preferred optimizer
strategy for statements that specify a select expression. The
following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If optimization strategy is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
3.10 – OPTIMIZE USING outline name
The OPTIMIZE USING clause explicitly names the query outline to
be used with the select expression even if the outline ID for the
select expression and for the outline are different.
See the CREATE OUTLINE statement for more information on creating
an outline.
3.11 – OPTIMIZE_WITH
Selects one of three optimzation controls: DEFAULT (as used by
previous versions of Rdb), AGGRESSIVE (assumes smaller numbers of
rows will be selected), and SAMPLED (which uses literals in the
query to perform preliminary estimation on indices).
The following example shows how to use this clause.
SQL> select * from employees where employee_id > '00200'
cont> optimize with sampled selectivity;
3.12 – order-by-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a description of the ORDER BY expression.
3.13 – PLACEMENT_ONLY_RETURNING
Syntax options:
PLACEMENT ONLY RETURNING DBKEY | PLACEMENT ONLY RETURNING ROWID
Returns the dbkey of a specified record, but does not insert
any actual data. The PLACEMENT ONLY RETURNING DBKEY clause lets
you determine the target page number for records that are to
be loaded into the database. When you use this clause, only the
area and page numbers from the dbkeys are returned. Use of this
clause can improve bulk data loads. If you use the PLACEMENT ONLY
clause, you can return only the dbkey values. Use the PLACEMENT
ONLY RETURNING DBKEY clause only in programs that load data into
an existing database and only with rows placed via a hashed index
in the storage map. For more information, see the Oracle Rdb
Guide to Database Design and Definition.
The keyword ROWID is a synonym to the DBKEY keyword.
3.14 – RETURNING value expr
Returns the value of the column specified in the values list. If
DBKEY or ROWID is specified, this argument returns the database
key (dbkey) of the row being added. (The ROWID keyword is a
synonym to the DBKEY keyword.) When the DBKEY value is valid,
subsequent queries can use the DBKEY value to access the row
directly.
The RETURNING DBKEY clause is not valid in an INSERT statement
used to assign values to the segments in a column of the LIST OF
BYTE VARYING data type.
3.15 – select-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
a description of the SELECT expression.
3.16 – select-expr
Specifies a select expression that specifies a result table.
The result table can contain zero or more rows. All the rows of
the result table are added to the target table named in the INTO
clause.
This is the only situation supported in SQL that allows you to
specify a second database in a single SQL statement.
The number of columns in the result table must correspond to the
number of columns specified in the list of column names. If you
did not specify a list of column names, the number of columns in
the result table must be the same as the number of columns in the
target table. For each row of the result table, the value of the
first column is assigned to the first column of the target table,
the second value to the second column, and so on.
You cannot specify a select expression in an INSERT statement
used to assign values to the segments in a column of the LIST OF
BYTE VARYING data type.
For detailed information on select expressions, see the Select_
Expressions HELP topic.
3.17 – VALUES value expr
Specifies a list of values to be added to the table as a single
row. The values can be specified through parameters, qualified
parameters, column select expressions, value expressions, or the
default values.
The values listed in the VALUES argument can be selected from
another table, but both tables must reside in the same database.
The number of values in the list must correspond to the number
of columns specified in the list of column names. If you did not
specify a column list, the number of values in the list must be
the same as the number of columns in the table. The first value
specified in the list is assigned to the first column, the second
value to the second column, and so on.
Values for IDENTITY, COMPUTED BY, and AUTOMATIC COLUMNS are not
able to be inserted so these column types are not considered for
the default column list.
See the SQL Online Help topic INSERT EXAMPLES for an example that
shows an INSERT statement with a column select expression.
4 – Examples
Example 1: Adding a row with literal values
The following interactive SQL example stores a new row in the
DEPARTMENTS table of the sample personnel database. It explicitly
assigns a literal value to each column in the row. Because the
statement includes the RETURNING DBKEY clause, SQL returns the
dbkey value 29:435:9.
SQL> INSERT INTO DEPARTMENTS
cont> -- List of columns:
cont> (DEPARTMENT_CODE,
cont> DEPARTMENT_NAME,
cont> MANAGER_ID,
cont> BUDGET_PROJECTED,
cont> BUDGET_ACTUAL)
cont> VALUES
cont> -- List of values:
cont> ('RECR',
cont> 'Recreation',
cont> '00175',
cont> 240000,
cont> 128776)
cont> RETURNING DBKEY;
DBKEY
29:435:9
1 row inserted
Example 2: Adding a row using parameters
This example is a COBOL program fragment that adds a row to the
JOB_HISTORY table by explicitly assigning values from parameters
to columns in the table. This example:
o Prompts for the column values.
o Declares a read/write transaction. Because you are updating
the JOB_HISTORY table, you do not want to conflict with other
users who may be reading data from this table. Therefore, you
use the protected share mode and the write lock type.
o Stores the row by assigning the parameters to the columns of
the table.
o Checks the value of the SQLCODE variable and repeats the
INSERT operation if the value is less than zero.
o Uses the COMMIT statement to make the update permanent.
STORE-JOB-HISTORY.
DISPLAY "Enter employee ID: " WITH NO ADVANCING.
ACCEPT EMPL-ID.
DISPLAY "Enter job code: " WITH NO ADVANCING.
ACCEPT JOB-CODE.
DISPLAY "Enter starting date: " WITH NO ADVANCING.
ACCEPT START-DATE.
DISPLAY "Enter ending date: " WITH NO ADVANCING.
ACCEPT END-DATE.
DISPLAY "Enter department code: " WITH NO ADVANCING.
ACCEPT DEPT-CODE.
DISPLAY "Enter supervisor's ID: " WITH NO ADVANCING.
ACCEPT SUPER.
EXEC SQL
SET TRANSACTION READ WRITE
RESERVING JOB_HISTORY FOR PROTECTED WRITE
END-EXEC
EXEC SQL
INSERT INTO JOB_HISTORY
(EMPLOYEE_ID,
JOB_CODE,
JOB_START,
JOB_END,
DEPARTMENT_CODE,
SUPERVISOR_ID)
VALUES (:EMPL-ID,
:JOB-CODE,
:START-DATE,
:END-DATE,
:DEPT-CODE,
:SUPER)
END-EXEC
IF SQLCODE < 0 THEN
EXEC SQL ROLLBACK END-EXEC
DISPLAY "An error has occurred. Try again."
GO TO STORE-JOB-HISTORY
END-IF
EXEC SQL COMMIT END-EXEC
Example 3: Copying from one table to another
This interactive SQL example copies a subset of data from the
EMPLOYEES table to an identical intermediate result table. To do
this, it uses a select expression that limits rows of the select
expression's result table to those with data on employees who
live in New Hampshire.
SQL> INSERT INTO TEMP
cont> (EMPLOYEE_ID,
cont> LAST_NAME,
cont> FIRST_NAME,
cont> MIDDLE_INITIAL,
cont> ADDRESS_DATA_1,
cont> ADDRESS_DATA_2,
cont> CITY,
cont> STATE,
cont> POSTAL_CODE,
cont> SEX,
cont> BIRTHDAY,
Cont> STATUS_CODE)
cont> SELECT * FROM EMPLOYEES
cont> WHERE STATE = 'NH';
90 rows inserted
SQL>
Example 4: Copying rows between databases with the INSERT
statement
This example copies the contents of the EMPLOYEES table from the
personnel database to another database, LOCALDATA.
SQL> ATTACH 'ALIAS PERS FILENAME personnel';
SQL> ATTACH 'ALIAS LOCALDB FILENAME localdata';
SQL>
SQL> DECLARE TRANSACTION
cont> ON PERS USING (READ ONLY
cont> RESERVING PERS.EMPLOYEES FOR SHARED READ)
cont> AND
cont> ON LOCALDB USING (READ WRITE
cont> RESERVING LOCALDB.EMPLOYEES FOR SHARED WRITE);
SQL>
SQL> INSERT INTO LOCALDB.EMPLOYEES
cont> SELECT * FROM PERS.EMPLOYEES;
100 rows inserted
SQL>
Example 5: Adding data to columns of data type LIST OF BYTE
VARYING
The following interactive SQL example adds a new row to the
RESUMES table of the sample personnel database. It first assigns
a value to the EMPLOYEE_ID column, then adds three lines of
information to the RESUME column of the same row. The RESUME
column has the LIST OF BYTE VARYING data type. You must specify
the name of the list column (RESUME) in addition to the table
column when declaring the table cursor for a positioned insert.
SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR SELECT EMPLOYEE_ID, RESUME
cont> FROM RESUMES;
SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR SELECT RESUME
cont> WHERE CURRENT OF TBLCURSOR;
SQL> OPEN TBLCURSOR;
SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID) VALUES ('00167');
1 row inserted
SQL> OPEN LSTCURSOR;
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('This is the resume for 00167');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Boston, MA');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Oracle Corporation');
SQL> CLOSE LSTCURSOR;
SQL> CLOSE TBLCURSOR;
SQL> COMMIT;
Example 6: Using the PLACEMENT ONLY RETURNING DBKEY clause of the
INSERT statement
SQL> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME)
cont> VALUES
cont> ('5000', 'Parsons', 'Diane')
cont> PLACEMENT ONLY RETURNING DBKEY;
DBKEY
56:34:-1
1 row allocated
SQL>
Example 7: Inserting the user name and an amount into table
columns:
SQL> CREATE TABLE TABLE1
cont> (ID CHAR(15),
cont> AMOUNT INT(4));
SQL> INSERT INTO TABLE1 (ID, AMOUNT)
cont> VALUES (USER, 1000);
SQL> SELECT * FROM TABLE1;
ID AMOUNT
ELLINGSWORTH 1000.0000
1 row selected
Example 8: Inserting a name and a column select expression into
the same table columns used in the previous example:
SQL> INSERT INTO TABLE1 (ID, AMOUNT)
cont> VALUES ('BROWN',
cont> (SELECT COUNT (*) FROM TABLE1));
SQL> SELECT * FROM TABLE1;
ID AMOUNT
HALVORSON 1000.0000
BROWN 1.0000
2 rows selected
Example 9: Inserting Default Values for Selected Columns
SQL> INSERT INTO DEPARTMENTS
cont> (DEPARTMENT_CODE, DEPARTMENT_NAME, BUDGET_ACTUAL)
cont> VALUES
cont> ('RECR','Recreation', DEFAULT);
1 row inserted
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_CODE='RECR';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
RECR Recreation NULL
NULL NULL
1 row selected
Example 10: Inserting a Row of All Default Values into a Table
SQL> INSERT INTO CANDIDATES
cont> DEFAULT VALUES;
1 row inserted
SQL> SELECT * FROM CANDIDATES
cont> WHERE LAST_NAME IS NULL;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
CANDIDATE_STATUS
RESUME
NULL NULL NULL
NULL
>>
>>
>>
NULL
1 row selected