import 1

[mgl70]

Hi,
How do I import data from a text file to multiple oracle tables. I have table structures.

My file has 50 columns and 15,000 records. These data should be loaded into 10 tables.

I know how to import into one table, but need to import into multiple tables. some columns go to one table some columns go to other table ....like that.

I donot even know how to search in the google also.

I appreciate all your help.

 

[hoinz]

Hi,

why not just import into one table, and repeat this task 10 times?

 

[SantaMufasa]

MGL,

Yours is a prime case for using Oracle's "Externally Organized" tables: a flat file that you can use as a read-only table in Oracle. For such a table, you need these components:

1) A flat file. In this case, to illustrate some typical flat-file behaviour, I am using a "comma-separated value" (CSV) file with a character other than a comma ("^") as my field delimiter:

2^123 Main St., Anytown, Anystate, USA
5^45 London Rd., Anytown, AnyCounty, UK

2) An Oracle "DIRECTORY" definition that discloses to Oracle where to look on your o/s for your external flat file:

create or replace directory yada as 'd:\dhunt\sqldba';

3) A definition of an Oracle table which data comes from an external flat file:

create table address (memberid number, address varchar2(100))
organization external
(Type oracle_loader
default directory yada
access parameters (fields terminated by '^' missing field values are null
(memberid, address))
location ('addresses.txt')
)
parallel reject limit 0
/

With the above three components, I can access my flat file as an Oracle table:

SQL> desc address
 Name                    Null?    Type
 ----------------------- -------- -------------
 MEMBERID                         NUMBER
 ADDRESS                          VARCHAR2(100)

SQL> select * from address;

  MEMBERID ADDRESS
---------- -------------------------------------
         2 123 Main St., Anytown, Anystate, USA
         5 45 London Rd., Anytown, AnyCounty, UK

2 rows selected.

In your case, you can then do either individual INSERTs into your 10 tables based upon the criteria/values that exist in your flat-file data, or you can create a PL/SQL block that reads the flat-file, external "table" data, then, using appropriate "IF" statements, distribute the rows to the appropriate table from your group of 10 target tables:

begin
    for r in (SELECT * FROM <flat-file-table>) loop
        IF r.<column-name> = '<criteria for T_1>' THEN
            INSERT into T_1 values
                (r.<T_1-column-name1 from flat-file-column>
                ,r.<T_1-column-name2 from flat-file-column>
                , et cetera);
        ELSIF r.<column-name> = '<criteria for T_2>' THEN
            INSERT into T_2 values
                (r.<T_2-column-name1 from flat-file-column>
                ,r.<T_2-column-name2 from flat-file-column>
                , et cetera);
        ELSIF ...<thru T_10's criteria;
    end loop;
end;
/

This method gives you very-fine-grained control over the logic you use to populate your 10 targe tables.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.