HP 3000 Manuals HP 3000 Manuals
Entering Data into Tables
Once you have created tables, you can get data into them in several ways.
Try the following two methods:
* The SQL INSERT command.
* The ISQL LOAD command.
Entering Data with the SQL INSERT Command
Use the SQL INSERT command to add rows to the tables you have created.
Try the following entry for the Albums table:
isql=> INSERT INTO Albums Return
> VALUES (2001, Return
> 'Serenades from the 17th Century', Return
> 'ca', 30.82, Return
> 'philips', '1988-12-18', Return
> '3456-AB-0998LS', Return
> 'Authentic original instruments'); Return
If the command does not complete successfully, check all your punctuation
carefully, and try again. When finished, COMMIT WORK.
Next, use the INSERT command to add the following row to the Titles table
(you need to build the INSERT command yourself):
AlbumCode: 2001
Selection: 'La Bella Musica'
Composer: 'Palestrina'
Timing: '0 00:21:12.000'
Performers: 'Ancient Music Group'
Comment: 'Lute improvisations'
Note that INSERT is an SQL command that processes a single row of data at
a time. If you want to insert many rows at a time, use an application
program of your own design, or else try the ISQL LOAD command, explained
below.
Entering Data with the ISQL LOAD Command
Use the ISQL LOAD command to insert data from an ordinary file into your
tables. Two kinds of LOAD operation are possible:
* LOAD INTERNAL.
* LOAD EXTERNAL.
The next sections show an example of each.
NOTE The sample external and internal files described in the next few
paragraphs are available on MPE XL 3.0 or later systems and on
HP-UX 8.0 or later systems. If you are using an earlier version of
ALLBASE/SQL, you should skip ahead to the section entitled
"Performing Queries."
LOADing from an INTERNAL File.
You use the LOAD command with the INTERNAL option to load data from a
file in INTERNAL format previously created by ISQL's UNLOAD command.
Your system contains an INTERNAL file with data for the Titles table. In
MPE XL, it is called TITLE.SAMPLEDB.SYS; in HP-UX, it is called
/usr/lib/allbase/hpsql/sampledb/title. From ISQL, issue the apppriate
command for your system:
On MPE XL:
isql=> LOAD FROM INTERNAL Return
> TITLE.SAMPLEDB.SYS Return
> TO Titles; Return
On HP-UX:
isql=> LOAD FROM INTERNAL Return
> /usr/lib/allbase/hpsql/sampledb/Title Return
> TO Titles; Return
As loading progresses, messages tell you how many rows have been
processed.
NOTE If you are loading a large file, be sure to set ISQL's AUTOCOMMIT
function to ON. For information, type HELP SET AUTOCOMMIT at the
ISQL prompt. For the present examples, AUTOCOMMIT is not needed.
LOADing from an EXTERNAL File.
Use the LOAD command with the EXTERNAL option to load data from plain
ASCII files into a table. You must enter the names of the columns in the
table you are loading into and the starting location in the file where
each data item starts, together with the data item's length. If the
column permits null values, the data file must contain null indicator
characters for any entry that is null. In the following example, the
question mark (?) is used as a null indicator.
From ISQL, issue one of the following commands, as appropriate for your
system. Be sure to type exactly.
On MPE XL:
isql=> LOAD FROM EXTERNAL Return
> ALBUM.SAMPLEDB.SYS to Albums Return
> AlbumCode 1 4 Return
> AlbumTitle 13 40 Return
> Medium 53 2 ? Return
> AlbumCost 55 6 ? Return
> RecordingCo 61 10 Return
> DateRecorded 71 10 ? Return
> MfgCode 89 40 ? Return
> Comment 137 80 ? Return
> END; Return
On HP-UX:
isql=> LOAD FROM EXTERNAL Return
> /usr/lib/allbase/hpsql/sampledb/Album Return
> to Albums Return
> AlbumCode 1 4 Return
> AlbumTitle 13 40 Return
> Medium 53 2 ? Return
> AlbumCost 55 6 ? Return
> RecordingCo 61 10 Return
> DateRecorded 71 10 ? Return
> MfgCode 89 40 ? Return
> Comment 137 80 ? Return
> END; Return
After you have entered the column descriptions, ISQL prompts you as
follows:
Load depending on value in input record (Y/N)>
Reply N to load all the values in the file. When the command completes,
issue a COMMIT WORK command:
isql=> commit work; Return
Note that the starting columns for each field of data are determined by
the actual position of the data in the file itself. Thus, using EXTERNAL
files, it is possible to load selected parts of each record.
For complete information about loading tables from INTERNAL and EXTERNAL
files, refer to the ALLBASE/ISQL Reference Manual.