HP 3000 Manuals HP 3000 Manuals
INSERT
The INSERT command adds rows to a table. The following two forms of the
INSERT command are described individually:
* The form used to add rows having values you define. You can add a
single row or (in an application program) you can insert multiple
rows using the bulk facility. There is special syntax for
prepared INSERT and BULK INSERT statements that use dynamic
parameter substitution.
* The form used to add rows defined by a SELECT command. This form
copies rows from one or more tables or views into a table and is
called a Type 2 INSERT.
Rules defined with a StatementType of INSERT will affect both forms of
INSERT command.
Scope
ISQL or Application Programs
SQL Syntax - Insert Rows with Defined Values
[BULK] INSERT INTO {[Owner.]TableName} [({ColumnName} [,...])] VALUES
{[Owner.]ViewName }
{SingleRowValues}
({BulkValues })
{? }
Parameters - Insert Rows with Defined Values
BULK is specified in an application program to insert
multiple rows with a single execution of the
INSERT command.
[Owner.]TableName identifies the table to which data is to be
added.
[Owner.]ViewName identifies a view on a single table; the data is
added to the table upon which the view is based.
Refer to the CREATE VIEW command for restrictions
governing insertion via a view.
ColumnName specifies a column for which values are supplied.
If you omit any of the table's columns from the
column name list, the INSERT command places the
default value of the respective column
definitions in the omitted columns. For columns
with no default value, the null value is placed
in the omitted columns. If the table definition
specifies NOT NULL for any of the omitted
columns, the INSERT command fails.
You can omit the column name list if you provide
values for all columns of the table in the same
order the columns were specified in the CREATE
TABLE (or CREATE VIEW) command.
VALUES The VALUES clause specifies the values
corresponding to the columns in the column name
list, or the columns specified in the CREATE
TABLE or CREATE VIEW commands, if no column name
list exists. Character and date/time literals
must be in single quotes.
SingleRowValues defines column values when you insert a single
row. The syntax for SingleRowValues is presented
separately below and includes single row syntax
for statements that do not use dynamic parameter
substitution.
BulkValues defines values when you use the BULK option. The
syntax for BulkValues is presented separately
below and includes bulk value syntax for
statements that do not use dynamic parameter
substitution.
? is a dynamic parameter value that defines column
values within a prepared insert statement that
uses dynamic parameter substitution. The syntax
for DynamicParameterValues is presented
separately below and includes both single row and
bulk processing for such statements.
SQL Syntax--SingleRowValues
The following syntax applies to single row inserts that do not use
dynamic parameter substitution.
{NULL }
{USER }
{:HostVariable [[INDICATOR] :IndicatorVariable]}
{? }
{:LocalVariable }
{:ProcedureParameter }
{::Built-inVariable }
{ConversionFunction } [,...]
{CurrentFunction }
{ {Integer} }
{[+] {Float } }
{[-] {Decimal} }
{ }
{'CharacterString' }
{0xHexadecimalString }
{'LongColumnIOString' }
Parameters--SingleRowValues
NULL indicates a null value.
USER evaluates to the current DBEUserID. In ISQL, it
evaluates to the logon name of the ISQL user.
From an application program, it evaluates to the
logon name of the individual running the program.
USER behaves like a CHAR(20) constant, with
trailing blanks if the logon name has fewer than
20 characters.
HostVariable contains a value in an application program being
input to the expression.
IndicatorVariable names an indicator variable, whose value
determines whether the associated host variable
contains a NULL value:
> = 0 the value is not NULL
< 0 the value is NULL (The value in the host
variable will be ignored.)
________________________________________________
NOTE To be consistent with the standard SQL and
to support portability of code, it is
strongly recommended that you use a -1 to
indicate a null value. However,
ALLBASE/SQL interprets all negative
indicator variable values to mean a null
value.
________________________________________________
? is a place holder for a dynamic parameter in a
prepared SQL statement in an application program.
The value of the dynamic parameter is supplied at
run time.
LocalVariable contains a value in a procedure.
ProcedureParameter contains a value that is passed into or out of a
procedure.
Built-inVariable is one of the following built-in variables used
for error handling:
* ::sqlcode
* ::sqlerrd2
* ::sqlwarn0
* ::sqlwarn1
* ::sqlwarn2
* ::sqlwarn6
* ::activexact
The first six of these have the same meaning that
they have as fields in the SQLCA in application
programs. Note that in procedures, sqlerrd2
returns the number of rows processed, for all
host languages. However, in application
programs, sqlerrd3 is used in COBOL, Fortran, and
Pascal, while sqlerr2 is used in C. ::activexact
indicates whether a transaction is in progress or
not. For additional information, refer to the
application programming guides and to the chapter
"Constraints, Procedures, and Rules."
ConversionFunction returns a value that is a conversion of a
date/time data type into an INTEGER or CHAR
value, or from a CHAR value.
CurrentFunction indicates the value of the current DATE, TIME, or
DATETIME function.
Integer specifies a value of type INTEGER or SMALLINT.
Float specifies a value of type FLOAT or REAL.
Decimal specifies a value of type DECIMAL.
CharacterString specifies a CHAR, VARCHAR, DATE, TIME, DATETIME,
or INTERVAL value.
HexadecimalString specifies a BINARY or VARBINARY value. If the
string is shorter than the target column, it is
padded with binary zeroes; if it is longer than
the target column, the string is truncated.
LongColumnIOString specifies the input and output locations for the
LONG data. The specification for this string is
given below.
SQL Syntax--LongColumnIOString
[{> } {FileName[.Group[.Account]]}]
[{>>} {CharString$ }]
< {FileName[.Group[.Account]]} [{>!} {CharString$CharString }]
{%HeapAddress:LengthofHeap } [ ]
[>%$ ]
Parameters--LongColumnIOString
FileName[.GroupName[.AccountName] ] is the location of the input file.
%HeapAddressValue is the heap address where the input
is located or where the output is
to be located.
LengthOfHeap is the length of the heap specified
for input. The length is used only
for the input portion of the
string.
> specifies that output is placed in
the following file. If the file
already exists, it is not
overwritten nor appended to, and an
error is generated.
>> specifies that output will be
appended to the following file
name. If the file does not exist,
it is created.
>! specifies that output is placed in
the following file name. If the
file already exists, it is
overwritten.
>%$ is a heap address, generated by
ALLBASE/SQL, where the output is
placed. This option cannot be used
with a file name.
$ is the wild card character that
represents a random 5 byte
alphanumeric character string in
file names.
Description--LongColumnIOString
* The input device must not be locked or have privilege security.
* An input device file can be a standard MPE/iX file with fixed
record size, valid blocking factor, valid file equations,
ASCII/binary option, and user labels option. Any related output
device file will have the same characteristics as the input device
file.
* When the input device is a heap address and no output device is
specified, output is a standard MPE/iX file with an 80-byte record
size, a blocking factor of 1, a binary option, and a file size
equal to the LONG column size.
When the input device is a file and no output device is specified,
ALLBASE/SQL generates a random file name with the same
characteristics as the input file specified in the LONG column I/O
string. If the input file contains labels, then the output file
contains the same amount of labels.
* When no portion of the output device name is specified, the
default file name, tmp$, is used. The wildcard character ($)
indicates a random, five-byte, alphanumeric character string.
This file is created in the local group.
* When you specify a portion of the output file name in conjunction
with the wildcard character $, a five-byte, alphanumeric character
string replaces the wildcard. The wildcard character can appear
in any position of the output device name except the first. The
maximum file name being 8 bytes, you can specify 3 bytes of the
device name, at least one of which is in the first character
position of the name and is not numeric.
* The wildcard character, whether user specified or part of the
default output device name, is an almost unique five-byte,
alphanumeric character string. The possibility of two identical
wildcards being generated is remote.
* When a file is used as the LONG column input or output device and
you do not specify a group and account name in the LONG column I/O
string, the default is the group and account in which your program
is running.
* The output device cannot be overwritten with a SELECT or FETCH
command unless you use the INSERT or UPDATE command with the
overwrite option.
SQL Syntax--BulkValues
The following syntax applies only to statements that do not use dynamic
parameter substitution.
:Buffer [,:StartIndex [, :NumberOfRows]]
Parameters--BulkValues
Buffer is a host array or structure containing rows that are
the input for the INSERT command. This array
contains elements for each column to be inserted and
indicator variables for columns that can contain null
values. Whenever a column can contain nulls, an
indicator variable must be included in the array
definition immediately after the definition of that
column. This indicator variable is an integer that
can have the following values:
> = 0 the value is not NULL
< 0 the value is NULL
____________________________________________________
NOTE To be consistent with the standard SQL and to
support portability of code, it is strongly
recommended that you use a -1 to indicate a
null value. However, ALLBASE/SQL interprets
all negative indicator variable values to mean
a null value.
____________________________________________________
StartIndex is a host variable whose value specifies the array
subscript denoting where the first row to be inserted
is stored in the array; default is the first element
of the array.
NumberOfRows is a host variable whose value specifies the number
of rows to insert; default is to insert from the
starting index to the end of the array.
Description--Insert Rows with SingleRowValues and BulkValues
* When you enter SQL commands interactively, you cannot use host
variables or the BULK option.
* You cannot use the BULK option in a procedure.
* If you omit any of the table's columns from the column name list,
the INSERT command places the default value of the respective
column definitions in the omitted columns. For columns with no
default value, the null value is placed in the omitted columns.
If the table definition specifies NOT NULL for any of the omitted
columns, the INSERT command fails.
* If ALLBASE/SQL detects an error during a BULK INSERT operation,
the error handling behavior is determined by the setting of the
SET DML ATOMICITY and SET CONSTRAINTS statements. Refer to the
discussion of these statements in this chapter for more
information.
* For CHAR and VARCHAR data, if a CharacterString literal is shorter
than the target column, it is padded with blanks; if it is longer
than the target column, the string is truncated. Refer to the
"Data Types" chapter for information on overflow and truncation of
other data types.
* No error or warning condition is generated by ALLBASE/SQL when a
character or binary string is truncated during an INSERT
operation.
* Using the INSERT command with views requires that the views be
based on queries that are updatable. See "Updatability of
Queries" in the chapter "SQL Queries."
* Values in referenced (primary key) columns must be inserted before
values in referencing (foreign key) columns. However, if you do a
bulk insertion, inserting the primary key rows after the foreign
key rows does not cause an error message, because the constraints
are satisfied by the time you COMMIT WORK.
* A table on which a unique constraint is defined cannot contain
duplicate rows.
* BINARY and VARBINARY data can be inserted in character or
hexadecimal format. Character format requires single quotes and
hexadecimal requires a 0x before the value.
* Under the default settings for the SET DML ATOMICITY and SET
CONSTRAINTS statements, integrity constraints on tables and views
are enforced on a statement level basis and if a constraint should
be violated during processing of the insert, no rows are inserted.
However, the SET DML ATOMICITY and SET CONSTRAINTS statements both
override the default behavior. For more information, it is
important that you refer to the section "Error Conditions in
ALLBASE/SQL" in the "Introduction" chapter, and the SET DML
ATOMICITY or the SET CONSTRAINTS statements in this chapter.
* Rows being inserted must not cause the search condition of the
table check constraint to be false and must cause the search
condition of the view check constraint to be true.
* Rows being inserted in the table through a view having a WITH
CHECK OPTION must satisfy the check constraint of the view and any
underlying views in addition to satisfying any constraints of the
table. Refer to the "Check Constraints" section in the
"Constraints, Procedures, and Rules" chapter for more information
on check constraints.
* Rules defined with a StatementType of INSERT will affect all kinds
of INSERT statements performed on the rules' target tables. When
the INSERT is performed, ALLBASE/SQL considers all the rules
defined for that table with the INSERT StatementType. If the rule
has no condition, it will fire for all rows affected by the
statement and invoke its associated procedure with the specified
parameters on each row. If the rule has a condition, it will
evaluate the condition on each row. The rule will fire on rows
for which the condition evaluates to TRUE and invoke the
associated procedure with the specified parameters for each row.
Invoking the procedure could cause other rules, and thus other
procedures, to be invoked if statements within the procedure
trigger other rules.
* If a DISABLE RULES statement is in effect, the INSERT statement
will not fire any otherwise applicable rules. When a subsequent
ENABLE RULES is issued, applicable rules will fire again, but only
for subsequent INSERT statements, not for those rows processed
when rule firing was disabled.
* In a rule defined with a StatementType of INSERT, any column
reference in the Condition or any ParameterValue will refer to the
value of the column as it is assigned in the INSERT statement, or
by the default value of the column if it is not included in the
INSERT statement.
* When a rule is fired by this statement, the rule's procedure is
invoked after the changes have been made to the database for that
row and all previous rows. The rule's procedure, and any chained
rules, will thus see the state of the database with the current
partial execution of the statement.
* If an error occurs during processing of any rule considered during
execution of this statement (including execution of any procedure
invoked due to a rule firing), the statement and any procedures
invoked by any rules will have no effect. Nothing will have been
altered in the DBEnvironment as a result of this statement or the
rules it fired. Error messages are returned in the normal way.
* The BULK option is not allowed within a procedure.
SQL Syntax--DynamicParameterValues
The following syntax applies to single row and bulk inserts that use
dynamic parameter substitution.
(? [,...] )
Parameters--DynamicParameterValues
(? [,...]) represents one or more host variables in a prepared
INSERT statement. Each ? corresponds in sequential
order to a column in the column name list of the
prepared statement (even when BULK is used).
When you use a data structure of sqlda_type to pass
dynamic parameter information between the application
and ALLBASE/SQL, the number of "?"s specified must
match the sqld field of the descriptor area and the
number of values in a single element of the data
buffer.
When you use host variables to pass dynamic parameter
data values between the application and ALLBASE/SQL,
the number of "?"s specified must match the number and
order of the host variables in the related EXECUTE
statement. This does not apply when you use the BULK
option as you cannot mix host variables and dynamic
parameters.
Description--Insert Rows with DynamicParameterValues
* Statements using question marks (?) indicating dynamic parameters
can be intermixed with items in SingleRowValues and they can
return either a value or a format. When using dynamic parameters
for values, the dynamic parameter becomes the data type of the
column. When using dynamic parameters for conversion functions,
they become the data type to which they are assigned (CHAR 72).
Only TO_DATE, TO_TIME, TO_DATETIME, and TO_INTERVAL are allowed
here; TO_CHAR and TO_INTEGER are not allowed.
* When using the BULK option, statements using question marks (?),
indicating dynamic parameters, can contain only question marks
(and no host variables) to indicate column input.
* The BULK option used with host variables is available for C,
COBOL, and FORTRAN applications.
* The BULK option used with an sqlda_type data structure is
available for C and Pascal applications.
* A detailed description of how to use this statement with dynamic
parameters is found in the ALLBASE/SQL Advanced Application
Programming Guide.
Authorization--Insert Rows with SingleRowValues and Bulk Values
If you specify the name of a table, you must have INSERT or OWNER
authority for that table or you must have DBA authority.
If you specify the name of a view, you must have INSERT or OWNER
authority for that view or you must have DBA authority. Also, the owner
of the view must have INSERT or OWNER authority with respect to the
view's base tables, or the owner must have DBA authority.
SQL Syntax--INSERT Rows Defined by a SELECT Command (Type 2 Insert)
INSERT INTO {[Owner.]TableName} [(ColumnName [,...])] QueryExpression
{[Owner.]ViewName }
Parameters--INSERT Rows Defined by a SELECT Command (Type 2 Insert)
[Owner.]TableName identifies the table to which data is to be
added.
[Owner.]ViewName identifies a view on a single table; the data is
added to the table upon which the view is based.
Refer to the CREATE VIEW command for restrictions
governing inserts via a view.
ColumnName specifies a column for which data is supplied
from the select list in the SELECT command. Each
column named must have a corresponding select
list item. You can omit the column name list if
you provide a select list item for all columns in
the target table in the same order the columns
were specified in the CREATE TABLE (or CREATE
VIEW) command.
QueryExpression defines the rows to be inserted based on one or
more tables and/or views in the DBEnvironment.
The name of the target table cannot appear within
the FROM clause or in a FROM clause of any
subquery. The query expression cannot contain an
INTO clause or a union operation.
The data types of each column in the select list
must be compatible with the data types of
corresponding columns in the target table. The
first select list item defines the first column
in the target table, the second select list item
defines the second column in the target table,
and so forth. The number of select list items
must equal the number of columns in the target
table.
Any column in the target table can contain null
values only if it was not defined with the NOT
NULL attribute. Therefore ensure either that
select list items are not null for any NOT NULL
target column, or that the NOT NULL target
columns have default values defined for them.
Description--INSERT Rows Defined by a SELECT Command (Type 2 Insert)
* You cannot use the ORDER BY clause in a Type 2 Insert.
* You cannot insert into a LONG column with this kind of INSERT
operation.
* You cannot specify a LONG column in the QueryExpression in this
kind of INSERT operation, except in a long column or string
function.
* If you omit any of the table's columns from the column name list,
the INSERT command places the default value of the respective
column definitions in the omitted columns. For columns with no
default value, the null value is placed in the omitted columns.
If the table definition specifies NOT NULL for any of the omitted
columns, the INSERT command fails.
* If ALLBASE/SQL detects an error during this kind of INSERT
operation, error handling behavior is determined by the setting of
the SET DML ATOMICITY and SET CONSTRAINTS statements. Refer to
the discussion of these statements in this chapter.
* Using the INSERT command with views requires that the views be
based on updatable queries. See "Updatability of Queries" in the
"SQL Queries" chapter.
* A table on which a unique constraint is defined cannot contain
duplicate rows.
* Under the default settings for the SET DML ATOMICITY and SET
CONSTRAINTS statements, integrity constraints on tables and views
are enforced on a statement level basis and if a constraint should
be violated during processing of the insert, no rows are inserted.
However, the SET DML ATOMICITY and SET CONSTRAINTS statements both
override the default behavior. For more information, it is
important that you refer to the section "Error Conditions in
ALLBASE/SQL" in the "Introduction" chapter, and the SET DML
ATOMICITY or the SET CONSTRAINTS statements in this chapter.
* Rows being inserted must not cause the search condition of the
table check constraint to be false and must cause the search
condition of the view check constraint to be true.
* Rows being inserted in the table through a view having a WITH
CHECK OPTION must satisfy the check constraint of the view and any
underlying views in addition to satisfying any constraints of the
table. Refer to the "Check Constraints" section of the
"Constraints, Procedures, and Rules" chapter for more information
on check constraints.
* Values in referenced (primary key) columns must be inserted before
values in referencing (foreign key) columns. However, if you do a
bulk insertion, inserting the primary key rows after the foreign
key rows does not cause an error message, as the constraints are
satisfied by the time you COMMIT WORK.
* BINARY and VARBINARY data can be inserted in character or
hexadecimal format. Character format requires single quotes and
hexadecimal requires a 0x before the value.
* Rules defined with a StatementType of INSERT will affect all kinds
of INSERT statements performed on the rules' target tables. When
the INSERT is performed, ALLBASE/SQL considers all the rules
defined for that table with the INSERT StatementType. If the rule
has no condition, it will fire for all rows affected by the
statement and invoke its associated procedure with the specified
parameters on each row. If the rule has a condition, it will
evaluate the condition on each row. The rule will fire on rows
for which the condition evaluates to TRUE and invoke the
associated procedure with the specified parameters for each row.
Invoking the procedure could cause other rules, and thus other
procedures, to be invoked if statements within the procedure
trigger other rules.
* If a DISABLE RULES statement is in effect, the INSERT statement
will not fire any otherwise applicable rules. When a subsequent
ENABLE RULES is issued, applicable rules will fire again, but only
for subsequent INSERT statements, not for those rows processed
when rule firing was disabled.
* In a rule defined with a StatementType of INSERT, any column
reference in the Condition or any ParameterValue will refer to the
value of the column as it is assigned in the INSERT statement, or
by the default value of the column if it is not included in the
INSERT statement.
* The set of rows to be inserted by a type 2 INSERT (that is, an
INSERT defined by a SELECT statement) is determined before any
rule fires, and this set remains fixed until the completion of the
rule. In other words, if the rule adds to, deletes from, or
modifies this set, such changes are ignored.
* When a rule is fired by this statement, the rule's procedure is
invoked after the changes have been made to the database for that
row and all previous rows. The rule's procedure, and any chained
rules, will thus see the state of the database with the current
partial execution of the statement.
* If an error occurs during processing of any rule considered during
execution of this statement (including execution of any procedure
invoked due to a rule firing), the statement and any procedures
invoked by any rules will have no effect. Nothing will have been
altered in the DBEnvironment as a result of this statement or the
rules it fired. Error messages are returned in the normal way.
Authorization--INSERT Rows Defined by a SELECT Command (Type 2 Insert)
To insert rows into a table, you must have INSERT or OWNER authority for
that table or you must have DBA authority.
To insert rows using a view, you must have INSERT or OWNER authority for
that view or you must have DBA authority. Also, the owner of the view
must have INSERT or OWNER authority with respect to the view's base
tables, or the owner must have DBA authority.
If you specify the name of a table in the FROM clause of the SELECT
command, you must have SELECT or OWNER authority for the table or you
must have DBA authority. If you specify the name of a view in the FROM
clause of the SELECT command, you must have SELECT or OWNER authority for
the view or you must have DBA authority. Also, the owner of the view
must have SELECT or OWNER authority with respect to the view's
definition, or the owner must have DBA authority.
Examples
1. Single-row insert
INSERT INTO PurchDB.Vendors
VALUES (9016,
'Secure Systems, Inc.',
'John Secret',
'454-255-2087',
'1111 Encryption Way',
'Hush',
'MD',
'00007',
'discount rates are carefully guarded secrets')
A new row is added to the PurchDB.Vendors table.
2. Bulk insert
BULK INSERT INTO PurchDB.Parts
(PartNumber, PartName)
VALUES (:NewRow, :Indx, :NumRow)
Programmatically, you can insert multiple rows with one execution
of the INSERT command if you specify the BULK option. In this
example, the rows to be inserted are in the array called NewRow.
3. Insert using SELECT operation
CREATE PUBLIC TABLE PurchDB.CalifVendors
(VendorName CHAR(30) NOT NULL,
PartNumber CHAR(16) NOT NULL,
UnitPrice DECIMAL(10,2),
DeliveryDays SMALLINT,
VendorRemarks VARCHAR(60) )
IN PurchFS
This table has the same column attributes as corresponding columns
in PurchDB.SupplyPrice and PurchDB.Vendors.
INSERT INTO PurchDB.CalifVendors
SELECT VendorName, PartNumber, UnitPrice, DeliveryDays, VendorRemarks
FROM PurchDB.Supplyprice, PurchDB.Vendors
WHERE PurchDB.SupplyPrice.VendorNumber = PurchDB.Vendors.VendorNumber
AND VendorState = 'CA'
Rows for California vendors are inserted based on a query result
obtained by joining PurchDB.SupplyPrice and PurchDB.Vendors. A
column list is omitted because all columns in the target table
have a corresponding select list item.
4. Single row insert using dynamic parameters with host variables
PREPARE CMD FROM 'INSERT INTO PurchDB.Parts (PartNumber, PartName)
VALUES(?,?);'
A new row is added to the PurchDB.Parts table based on the
prepared INSERT statement called CMD. Row values are provided at
run time, and an EXECUTE statement using two host variables is
required to complete the INSERT.
EXECUTE CMD USING :PartNumber, :PartName;
5. Bulk insert using dynamic parameters with host variables
PREPARE CMD FROM 'BULK INSERT INTO PurchDB.Parts (PartNumber, PartName)
VALUES(?,?);'
Multiple rows can be added to the PurchDB.Parts table. Row values
are provided at run time, and an EXECUTE statement using the
address of a host variable array containing dynamic parameter data
and host variables containing the starting index and number of
rows to be inserted complete the INSERT.
EXECUTE CMD USING :DataBuffer, :StartIndex, :NumberOfRows;
6. Bulk insert or single row insert using dynamic parameters with
sqlda_type and related data structures
PREPARE CMD FROM 'BULK INSERT INTO PurchDB.Parts (PartNumber, PartName)
VALUES(?,?);'
One or more rows can be added to the PurchDB.Parts table. Row
values are provided at run time, and an EXECUTE statement using a
descriptor area is required to complete the INSERT.
Before issuing the execute statement, you must set certain fields
in the descriptor area. (The ALLBASE/SQL application programming
guides contain detailed information regarding this technique.)
Then you describe the input to ALLBASE/SQL.
DESCRIBE INPUT CMD INTO Sqlda;
EXECUTE CMD USING DESCRIPTOR Sqlda;