SQL-J Language Reference
Page 37 of 121

INSERT statement

An INSERT statement creates a row or rows and stores them in the named table.

Syntax

INSERT INTO { TableName | ExternalVirtualTable }
    [ ( SimpleColumnName [ , SimpleColumnName]* ) ]
[ PROPERTIES clause ]
Query

Query can be:

  • a SelectExpression
  • a VALUES list
  • a multiple-row VALUES expression

    Single-row and multiple-row lists 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.

  • UNION expressions

For more information about Query, see Query.

The only valid properties for a PROPERTIES clause in an INSERT statement are insertMode property and optionally the bulkFetch property. See Bulk Insert Properties.

NOTE: There is no transaction support for inserting into or deleting from an external virtual table; rolling back a statement that inserted into or deleted from an ExternalVirtualTable does not roll back the underlying insert or delete.

Example

INSERT INTO Countries
VALUES ('Algeria', 'DZ', 'North Africa')

INSERT INTO SouthAmericanCountries
SELECT *
FROM Countries WHERE region = 'South America'

-- You can insert the results of a subquery into a table,
-- even if the subquery references
-- that table.
-- if DEFAULT values are defined for any non-specified
-- columns, the default values are automatically
-- inserted
INSERT INTO Flights (flight_id, segment_number)
    (SELECT flight_id, segment_number+3
FROM Flights
WHERE orig_airport = 'SFO')

-- use the fast import mode to insert into the
-- HotelAvailability table
INSERT INTO HotelAvailability
PROPERTIES insertMode=bulkInsert, bulkFetch=64
SELECT *
FROM NEW FileImport('ha.txt') AS EVT;

-- use the fast import mode to transfer data from
-- one table to another
INSERT INTO SouthAmericanCountries
PROPERTIES insertMode=replace
SELECT *
FROM Countries
WHERE region = 'South America'

-- use the DEFAULT keyword to insert DEFAULT values
INSERT INTO HotelAvailability
VALUES (10, '2001-01-01', DEFAULT)

-- Insert the DEFAULT value for a column by
-- omitting an explicit value
INSERT INTO HotelAvailability (hotel_id, booking_date)
VALUES (10, '2001-01-01')

-- inserting into a read-write VTI
INSERT INTO NEW JBMSTours.vti.jdbc1_2.ExternalCloudscapeTable(
'jdbc:cloudscape:history', 'hotelbookings')
VALUES (1000, 500, current_date, current_date, 10, 10.00)

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 query. 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.

The INSERT depends on all aliases used in the query. Dropping aliases invalidates a prepared INSERT statement if the statement uses the alias.

Interaction with Java Data Types

Each built-in type in SQL-J has a Java class associated with it. You can insert a value of a built-in type into a column of its corresponding Java class, and vice versa. Table 1-11, "Java Classes Associated with SQL-J Built-In Types", shows the correspondence between types.

For example, you can insert an SQL-J INTEGER value into a SERIALIZE(java.lang.Integer) column, and you can insert a java.lang.Integer value into an SQL-J INTEGER column.

When inserting into a column that stores a Java data type, Cloudscape checks whether the class of the object being inserted is assignable to the type of the column. If the object's class is not assignable to the column's Java data type, Cloudscape throws the statement exception:

An attempt was made to put a data value of type "{0}" into a data value of type "{1}".

For more information, see Assignability.