Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Bulk Insert from a flat file ?

    Hi,

    How to do a bulk insert from a flat file.

    Below is a way to do that :

    create table test
    (EMPNO NUMBER(4))
    ORGANIZATION EXTERNAL
    ( type oracle_loader
    default directory data_dir
    access parameters
    ( fields terminated by ',' )
    location ('emp.dat')
    )

    But I need a seperate query to load from a flat file, apart from CREATE table statement.

    Please advice,
    Sam

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    After you create the external table, did you try "select * from test"

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    Actually I didn't even tried this query. coz, this is not my requirement, as I said, I need a seperate query to bulk insert the data from a flat file. Is there anything like this in Oracle ?

    Thanks,
    Sam.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    you create the external table, right? Well, how do you get data from one table to another (in bulk)? Think, think, think. Oh yeah, "insert into a select * from b".

  5. #5
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    I need to get the data from a flat file and not from another table.

    Thanks,
    Sam

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    Okay, this is my last post. Now, you started this whole thing with the create external table command. Do you have clue what that does? Did you read the doc? It creates a table which isn't really a table, but instead is something that looks and acts like a table while reading data from your file. So, the external table (you named it TEST, not me) will allow "select * from test". This reads the data from the flat file. Get it! Then you can do whatever you want with it. Select it, insert it to some other table, loop over it in pl/sql, use the break and column commands to make a report. You can select from the external table (really the flat file) all you want - but you can't update, delete or insert to external tables.

  7. #7
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Hahaha, shoblock, take it easy...

    SamCute, once you've created this table you should copy emp.dat file to the <data_dir> folder.

    After that just run a "select * from test;" from inside SQL*Plus, if you want bulk insert, just do "insert into emp select * from test;"

    Saludos...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The "real" answer is to use SQLLDR utility after the table exists in the DB.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    not sure if you're joking or not. Why would you use sqlloader is the you already have an external table?

    ---------------------
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •