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 statement.
The ISQL LOAD command.

Entering Data with the SQL INSERT Statement

Use the SQL INSERT statement 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 statement does not complete successfully, check all your punctuation carefully, and try again. When finished, COMMIT WORK.

Next, use the INSERT statement to add the following row to the Titles table (you need to build the INSERT statement 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 statement 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/iX, it is called TITLE.SAMPLEDB.SYS; in HP-UX, it is called /usr/lib/allbase/hpsql/sampledb/title. From ISQL, issue the appropriate command for your system:

On MPE/iX:

   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/iX:

   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 statement:

   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.

Entering Data into Tables

Technical documentation

» Table of Contents

» Index

Entering Data with the SQL INSERT Statement

Entering Data with the ISQL LOAD Command

LOADing from an INTERNAL File

LOADing from an EXTERNAL File