Re: SQL*LOADER different types of records

kristofvdw_at_matt.es (K) wrote:

>Hi,
>
>I have to extract 3 types of records (header-details-footer,
>reoccuring) from a flat text file into three tables.
>The problem is that the only way to distinguish the records is by the
>number of fields (or maybe even the types), since there is no
>indicator of recordtype nor fixed record size. I cannot change this
>design since it is given by an external company.
>I would need a control file like this one (simplified):
>
>LOAD DATA
>APPEND
>INTO TABLE TABLE1
>WHEN COUNTER = '2'
>FIELDS TERMINATED BY X'09'
>(
> c1,
> c2
>)
>
>INTO TABLE TABLE2
>WHEN COUNTER = '5'
>FIELDS TERMINATED BY X'09'
>(
> c1,
> c2,
> c3,
> c4,
> c5
>)
>
>INTO TABLE TABLE3
>WHEN COUNTER = '3'
>FIELDS TERMINATED BY X'09'
>(
> c1,
> c2,
> c3
>)
>
>But how do I get the "COUNTER" value?
>
>Any suggestion is more than welcome, I've been struggeling with this
>one for days already...
>
>TIA,
>K

I am not sure you can with SqlLoader..
[Quoted] You could create an interim table with the number of columns equal to the maximum # of fields that can be in the load file and set the default value of all the columns to NULL. After loading the data use the NULL values to test as to which table to insert the records into..

Like
Update table3 select c1,c2,c3 from interim_table where c4 is NULL and c5 is NULL; Received on Fri Oct 22 2004 - 15:40:15 CEST