Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Albuquerque, NM
    Posts
    5

    Unanswered: Inserting multiple rows with single statement?

    I am trying to insert a few thousand rows into an oracle table, and individual insert statements is not working for me. I get the parse error ORA-00911 when using PHP, and it takes quite a while to use something like TOAD.

    What I am doing is:
    Code:
    INSERT INTO TABLE_NAME (COL1, COL2) VALUES ('val1', 'val2');
    INSERT INTO TABLE_NAME (COL1, COL2) VALUES ('val11', 'val22');
    INSERT INTO TABLE_NAME (COL1, COL2) VALUES ('val111', 'val222');
    INSERT INTO TABLE_NAME (COL1, COL2) VALUES ('val1111', 'val2222');
    Is there something similar to the following that will work in oracle?
    Code:
    INSERT INTO TABLE_NAME (COL1, COL2) VALUES 
    ('val1', 'val2')
    ('val11', 'val22')
    ('val111', 'val222')
    ('val1111', 'val2222')
    I have searched a few manuals but have not found this, and I am not the type to experiment while using other people's oracle servers. Any tips?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Create an SQL*Loader control file and execute the utility:
    Code:
    LOAD DATA
    INFILE *
    INTO TABLE TABLE_NAME TRUNCATE
    FIELDS TERMINATED BY ',' OPTIONALY ENCLOSED BY "'"
    (COL1, COL2) 
    BEGINDATA
    'val1', 'val2'
    'val11', 'val22'
    'val111', 'val222'
    'val1111', 'val2222'
    ...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2003
    Location
    Albuquerque, NM
    Posts
    5
    Cool, thanks.

    I was just hoping there was a simpler way of doing this with an insert statement so I could use my scripting language instead of manually loading files.

    After further investigation I discovered the BEGIN and END; block to execute multiple inserts.

    It parses fine now, but still won't execute. But that is probably a PHP problem.

    Thanks again for the reply!

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could dynamically create the SQL*Loader file and execute the utility with PHP.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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