Oracle7 Server Utilities

Using Multiple INTO TABLE Statements

Multiple INTO TABLE statements allow you to:

load data into different tables

extract multiple logical records from a single input record

distinguish different input record formats

In the first case, it is common for the INTO TABLE statements to refer to the same table. This section illustrates the different ways to use multiple INTO TABLE statements and shows you how to use the POSITION keyword.

Note: A key point when using multiple INTO TABLE statements is that field scanning continues from where it left off when a new INTO TABLE statement is processed. The remainder of this section details important ways to make use of that behavior. It also describes alternative ways using fixed field locations or the POSITION keyword.

Extracting Multiple Logical Records

Some data storage and transfer media have fixed-length physical records. When the data records are short, more than one can be stored in a single, physical record to use the storage space efficiently.

In this example, SQL*Loader treats a single physical record in the input file as two logical records and uses two INTO TABLE clauses to load the data into the EMP table. For example, if the data looks like

1119 Smith      1120 Snyder

1121 Spellini   1130 Thompson

then the following control file extracts the logical records:

INTO TABLE emp

	(empno POSITION(1:4)  INTEGER EXTERNAL,

	 ename POSITION(6:15) CHAR)

INTO TABLE emp

	(empno POSITION(17:20) INTEGER EXTERNAL,

	 ename POSITION(21:30) CHAR)

Relative Positioning

The same record could be loaded with a different specification. The following control file uses relative positioning instead of fixed positioning. It specifies that each field is delimited by a single blank (" "), or with an undetermined number of blanks and tabs (WHITESPACE):

INTO TABLE emp

	(empno INTEGER EXTERNAL TERMINATED BY " ",

	 ename CHAR             TERMINATED BY WHITESPACE)

INTO TABLE emp

	(empno INTEGER EXTERNAL TERMINATED BY " ",

	 ename CHAR)            TERMINATED BY WHITESPACE)

The important point in this example is that the second EMPNO field is found immediately after the first ENAME, although it is in a separate INTO TABLE clause. Field scanning does not start over from the beginning of the record for a new INTO TABLE clause. Instead, scanning continues where it left off.

To force record scanning to start in a specific location, you use the POSITION keyword. That mechanism is described next.

Distinguishing Different Input Record Formats

A single datafile might contain records in a variety of formats. Consider the following data, in which EMP and DEPT records are intermixed:

1 50   Manufacturing				-- DEPT record

2 1119 Smith      50				-- EMP record

2 1120 Snyder     50

1 60   Shipping

2 1121 Stevens    60

A record ID field distinguishes between the two formats. Department records have a "1" in the first column, while employee records have a "2". The following control file uses exact positioning to load this data:

INTO TABLE dept

	WHEN recid = 1

	(recid  POSITION(1:1)  INTEGER EXTERNAL,

	 deptno POSITION(3:4)  INTEGER EXTERNAL,

	 ename  POSITION(8:21) CHAR)

INTO TABLE emp

	WHEN recid <> 1

	(recid  POSITION(1:1)   INTEGER EXTERNAL,

	 empno  POSITION(3:6)   INTEGER EXTERNAL,

	 ename  POSITION(8:17)  CHAR,

	 deptno POSITION(19:20) INTEGER EXTERNAL)

Relative Positioning

Again, the records in the previous example could also be loaded as delimited data. In this case, however, it is necessary to use the POSITION keyword. The following control file could be used:

INTO TABLE dept

	WHEN recid = 1

	(recid  INTEGER EXTERNAL TERMINATED BY WHITESPACE,

	 deptno INTEGER EXTERNAL TERMINATED BY WHITESPACE,

	 dname  CHAR TERMINATED BY WHITESPACE)

INTO TABLE emp

	WHEN recid <> 1

	(recid  POSITION(1) INTEGER EXTERNAL TERMINATED BY ' ',

	 empno  INTEGER EXTERNAL TERMINATED BY ' '

	 ename  CHAR TERMINATED BY WHITESPACE,

	 deptno INTEGER EXTERNAL TERMINATED BY ' ')

The POSITION keyword in the second INTO TABLE clause is necessary to load this data correctly. This keyword causes field scanning to start over at column 1 when checking for data that matches the second format. Without it, SQL*Loader would look for the RECID field after DNAME.

Loading Data into Multiple Tables

By using the POSITION clause with multiple INTO TABLE clauses, data from a single record can be loaded into multiple normalized tables. Case 5

illustrates this concept.

Summary

Multiple INTO TABLE clauses allow you to extract multiple logical records from a single input record and recognize different record formats in the same file.

For delimited data, proper use of the POSITION keyword is essential for achieving the expected results.

When the POSITION keyword is not used, multiple INTO TABLE clauses process different parts of the same (delimited data) input record, allowing multiple tables to be loaded from one record. When the POSITION keyword is used, multiple INTO TABLE clauses can process the same record in different ways, allowing multiple formats to be recognized in one input file.