Re: SQL*Loader. 1 Datafile in several Tables???

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/18
Message-ID: <33a8025e.6597066_at_www.sigov.si>#1/1

On 17 Jun 1997 10:03:03 -0700, ralfkret_at_hotmail.com wrote:

>My task is to load a huge file into a database. The file should end up in
>serveral tables. The file has delimited fields in it.
>I cannot find a way to address a specific field other than the first one in an
>import record.
>
>A sample Import file might look like this one
>
> [SNIP ... sample control file ... SNIP]
>
>Is there any way to skip a field in the import file or to address it by field
>number rather than by character number.
>The only alternativ seems to be to change the import file.
>
>Thanks for your help.

You can't reference field by it's sequence number, you can reference it only with its absolute position or with position, relative to previously referenced field.

When you load single phisycal record into multiple tables (with many INTO TABLE clases in the same control file), the position of the first field in any subsequent INTO TABLE clause is relative to the last referenced field in the previous INTO TABLE clause, unles you specify the first field with its absolute position in the physical record.

In your case, you specify the first field of the second INTO TABLE with absolute position (1), and naturaly the next relative position is relative to this first field. To omit this problem, you must first specify your sText2 field, relative to sText of your previous INTO TABLE, and only then reference nID field with absolute position. That way, you'll be able to load both tables with the same control file.

Here is your corrected control file:

LOAD DATA
INFILE *
REPLACE
INTO TABLE imp1
FIELDS TERMINATED BY ','

	(
	nID,
        sText
	)

INTO TABLE imp2
FIELDS TERMINATED BY ','

	(
-- ORIGINAL	nID Position(1), -- reposition on first field
-- ORIGINAL	sText2 -- should be 3rd field but is always the 2nd 
        sText2 POSITION(*),	-- immediate folllowing sText field
        nID POSITION(1)		-- in absolute position (1)
	)

BEGINDATA

1,aXXXXXXXXXXX,z
2,bXXXXXXXX,x
3,cXXXXXXX,y
4,dXXXXXXXXXX,w

Regards,

---

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

---

Received on Wed Jun 18 1997 - 00:00:00 CEST