ALLBASE/SQL Reference Manual (36216-90216)

456 Chapter11
SQL Statements E - R
INSERT
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(?,?);'