RDB/VMS SQL INSERT — VMS RDB_4.2
INSERT INTO -+-> table-name ----------+----+
+-> view-name -----------+ |
+-> CURSOR cursor-name -+ |
+----------------------------------------+
++---------------------------+-+
+-> ( -+> column-name +-> ) + |
+------ , <----+ |
+-----------------------------+
+-> VALUES -> ( -++> literal -----------++-> ) +
| |+> parameter ---------+| |
| |+> col-select-expr ---+| |
| |+> value-expr --------+| |
| |+> NULL --------------+| |
| |+> USER --------------+| |
| |+> CURRENT_DATE ------+| |
| |+> CURRENT_TIME ------+| |
| |+> CURRENT_TIMESTAMP -+| |
| +------- , <------------+ |
| +-------------------------------------------+
| +-+----------------------------------+---+---> ;
| +-> returning-clause --------------+ |
+-> select-expr ----------------------------+
Additional information available:
More Informationvalue expr clausenumeric value expr clausecommon value expr clause
date time value expr clausechar value expr clauseExamplesPositioned INSERT
List INSERTcolumn namereturning clauseselect expr
More Information
The INSERT statement stores data in the rows of a table. If you are storing data of data type LIST OF BYTE VARYING in your table, use a positioned insert to store one or more rows in an insert only table cursor. The positioned insert sets up the proper row context for a subsequent insert list cursor. After establishing row context, use a list insert to store data in the elements of a list within that table row. The positioned insert and list insert require special subsets of the INSERT statement syntax. For syntax, see Positioned_INSERT and List_INSERT.
value expr clause
value-expr = -+-+-++------+-+---> numeric-value-expr ------------+-+----------+-+-> | | |+-> + -+ +---> date-time-value-expr ----------+ +-> + -+-+ | | | | || | | | | | | | | |+-> - -+ +-> - -+ | | | | | | +-> * -+ | | | | | | | | | | | | | | +-> / -+ | | | | | +-----------------------------------------------<---------+ | | | +----------+----> char-value-expr --------------+-------------+ | | +----------- || ----------- <--------+ | +--> DBKEY -------------------------------------------------------+
numeric value expr clause
numeric-value-expr = --+---> common-value-expr -----------------------+--> +---> numeric-literal -------------------------+ +---> CHARACTER_LENGTH -+- (value-expr) -------+ +---> CHAR_LENGTH ------+ | +---> OCTET_LENGTH (value-expr) ---------------+ +---> EXTRACT ( date-time-field ------+ | | +--------------------------------+ | | +---> FROM date-time-value-expr ) --------+ | | +---> (numeric-value-expr) --------------------+
common value expr clause
common-value-expr =
--+---> column-name ------------------------------------+-->
+---> parameter --------------------------------------+
+---> (col-select-expr) ------------------------------+
+---> CAST (cast-operand AS --+-> data-type ----+- ) -+
+-> domain-name --+
date time value expr clause
date-time-value-expr = --+---> common-value-expr -----------------------------+-> +---> date-time-literal -----------------------------+ +---> CURRENT_DATE ----------------------------------+ +---> CURRENT_TIME ------+-------------------------+-+ | +--> (time-precision) ----+ | | | +---> CURRENT_TIMESTAMP -+--------------------------++ | +-> (timestamp-precision) -+| | | +---> (date-time-value-expr) ------------------------+
char value expr clause
char-value-expr =
---+-> common-value-expr -----------------------------------+->
+-> string-literal --------------------------------------+
+-> USER ------------------------------------------------+
+-> UPPER (char-value-expr) -----------------------------+
+-> LOWER (char-value-expr) -----------------------------+
+-> TRANSLATE (char-value-expr USING translation-name) --+
+-> SUBSTRING (char-value-expr FROM --+ |
| +-----------------------------------+ |
| +-> start-position --+-----------------------+-- ) ----+
| +-> FOR string-length --+ |
+-> (char-value-expr) -----------------------------------+
Examples
Example 1: Inserting the user name and an amount into table
columns:
SQL> CREATE TABLE TABLE1 (ID CHAR(15),
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 2: 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 3: Inserting a string literal that includes both single
and double quotation marks. Note that you must use two
consecutive single quotation marks to output a single quotation
mark within a string.
SQL> CREATE TABLE TEST_TABLE (TEST_COL CHAR (22));
SQL> INSERT INTO TEST_TABLE (TEST_COL) VALUE ('Richard -
cont> "Rick" Smith''s');
SQL> SELECT * FROM TEST_TABLE;
TEST_COL
Richard "Rick" Smith's
Positioned INSERT
INSERT INTO CURSOR ---> cursor-name --+
+----------------------------------+
+-+----------------------------+---+
+-> ( -+ column-name -+-> ) -+ |
+------ , <----+ |
+----------------------------------+
|
+-> VALUES --> ( -++-> literal ---++--> ) --->;
|+-> parameter -+|
|+-> NULL ------+|
|+-> USER ------+|
+---- , <--------+
List INSERT
INSERT INTO CURSOR ---> cursor-name --+
|
+------------------------------------+
+--> VALUES --> ( -++-> literal ---++-> ) --> ;
|+-> parameter -+|
+----- , <-------+
column name
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 defined with the NOT NULL clause in the CREATE TABLE statement. You must include in an INSERT statement all names of columns defined with the NOT NULL clause. 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.
returning clause
returning-clause = -+-----------------------------------++------------------+> +-> RETURNING value-expr -----------++> INTO parameter -+ +-> PLACEMENT ONLY RETURNING DBKEY -+
Additional information available:
PLACEMENT_ONLY_RETURNING_DBKEY
RETURNING value exprINTO parameter
RETURNING value expr
Returns the value of the column specified in the values list. If DBKEY is specified, returns the database key (DBKEY) of the row being added. 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. The value-expr is any value expression as shown in the syntax diagram.
PLACEMENT_ONLY_RETURNING_DBKEY
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 only return 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.
INTO parameter
Inserts the value specified to a specified parameter. The INTO parameter clause is not valid in interactive SQL.
select expr
select-expr =
-+-+-> select-clause --------------+-+-+--------------------+-+
| +->( select-expr ) -------------+ | +-> order-by-clause -+ |
| +->( select-expr-standard ) ----+ | |
| +---------<-----------------------+ |
| +->UNION -+-------+-------------+ |
| +->ALL -+ | |
+-------<-------------------------+ |
+------------------------------------------------------------+
+-+--------------------+----------+--------------------+------>
+-> limit-to-clause -+ +-> optimize-clause -+
Additional information available:
More Information
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. 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. The value of the first column in the result table is assigned to the first column of the target table, the second value to the second column, and so on.
select clause
select-clause =
SELECT -+------>------+-> select-list --+
+-> ALL ------+ |
+-> DISTINCT -+ |
+------------------------------<--------+
+-> FROM -++-> table-name -++----->-------------++-+
|+-> view-name --++> correlation-name +| |
+---------- , <------------------------+ |
+--------------------------------------------------+
++--------->--------++------------->--------------++
+> WHERE predicate ++> GROUP BY -+> column-name ++|
+------- , <---+ |
+--------------------------------<-----------------+
++---------->----------+---------------------------->
+-> HAVING predicate -+
For more information on select expressions, see the online help
topic on select_expr.
UNION
The union operator merges the results of a select expression or select clause with another select expression or select clause into one result table by appending the values of columns in one table with the values of columns in other tables.
Additional information available:
ALL
The ALL qualifier specifies that duplicate rows should not be eliminated from the result table.
VALUES
The VALUES clause specifies a list of values to be added to the table as a single row. The values can be specified through literals, parameters, column select expressions, and the keywords NULL, USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP. 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, and so on.
Additional information available:
col select expr
col-select-expr =
-+-+-> col-select-clause -+-+--------------------+-+
| +->( col-select-expr )-+ +-> order-by-clause -+ |
| +---------<------------+ |
| +-> UNION -+-------+----+ |
| +> ALL -+ | |
+--------<----------------+ |
+-------------------------------------------------+
+-+--------------------+--------------------------->
+-> limit-to-clause -+
Additional information available:
col select clause
col-select-clause =
SELECT -+------>------+-+-> * -----------------------------+ FROM +
+-> ALL ------+ +-> table-name -------+-> .* ------+ |
+-> DISTINCT -+ +-> view-name --------+ | |
| +-> correlation-name -+ | |
| +-> value-expr --------------------+ |
++------+-+---> SUM ---++-> (DISTINCT column-name) + |
+-> + -+ +---> AVG ---++-> (ALL value-expr) ------+ |
+-> - -+ +---> MAX ---+ | |
+---> MIN ---+ | |
+---> COUNT -+--> (*) -------------------+ |
+--> (DISTINCT column-name) + |
+--------------------------------<------------------------------+
+++-> table-name ++--------------------++-+-------->-----------++
|+-> view-name -++-> correlation-name +| +-> WHERE predicate -+|
+---------- , <------------------------+ |
+------------------------------<--------------------------------+
++------------->------------------+-+---------->----------+-->
+-> GROUP BY -+-> column-name -+-+ +-> HAVING predicate -+
+------- , <-----+
UNION
The UNION operator merges the results of a select expression or select clause with another select expression or select clause into one result table by appending the values of columns in one table with the values of columns in other tables.
ALL
The ALL qualifier specifies that duplicate rows should not be eliminated from the result table.