Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Unhappy Unanswered: multiple row insertion in oracel

    Hi,

    is multiple row insert by sql statement supported by oracle?

    how come when I run the following statement:
    insert into BRIDES_LOCAL_REPORT(environment, event_code)
    values ('dev' , 'vd-restult'), ('prod', 'imp' );

    I got a error: ORA-009333: SQL command not properly ended


    This is such a simple statement. I just don't understand what's wrong with it.

    Please help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, it isn't supported.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    GOOGLE is your friend, but only when you actually use it!
    Results 1 - 10 of about 836,000 for oracle multiple row insert
    http://www.thescripts.com/forum/thread64158.html
    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.

  4. #4
    Join Date
    Oct 2007
    Posts
    2

    Unhappy

    thanks! I can't use insert....select from ... statement like what's in the article:

    Insert into DEF (EMPID ,MOD ,KKK)
    SELECT EMPID*x*y , 0 , 786 FROM ABC;

    The values I want to insert into DB are in my objects. So back to my first question, what was the problem with my SQL statement? and is multiple row insertion supported by oracle? (according to one of the search result of google, it is not)

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by adellalin
    and is multiple row insertion supported by oracle? (according to one of the search result of google, it is not)
    See andrew's answer.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    scott@ORA92> insert ALL
      2  INTO NR_ATG_ORDER_REL VALUES ('o27290008', 0, 'order1001023')
      3  INTO NR_ATG_ORDER_REL VALUES ('o27400001', 0, 'order1001071')
      4  INTO NR_ATG_ORDER_REL VALUES ('o27400002', 0, 'order1001073')
      5  SELECT * FROM DUAL
      6  /
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Very neat!

Posting Permissions

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