Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: How to Insert multiple values from a single query

    I want to do multiple insert in a single query in Oracle 8i.

    INSERT INTO tablename (column1, column2)
    VALUES ('val1', 'val2')

    It works fine no problem.

    But when I want to add multiple values it doesnot work.
    INSERT INTO tablename (column1, column2)
    VALUES (('val1', 'val2'), ('val3', 'val4'),('val5', 'val6'))

    Any idea how to do multiple values insert through a single query in Oracle?

    fuddoo

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This looks like MySQL .

    AFAIK, this is not possible in Oracle : you have to do one insert at a time. However, if data are in a flat file, you can use SQLLoader to do that in one shot.

    And if data are in another table, just do something like :
    Code:
    INSERT INTO tablename (column1, column2)
    select (col10, col11) 
    from tablename2
    where ...
    HTH and Regards,

    RBARAER

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    Thanx for the prompt reply!
    In my case data is coming from another table but both the value are
    manipulated and they are dependent on each other. This is done through
    JDBC/JAVA.

    Basically the source tables contains object_id , ref_object_id to make a tree like structure(parent/child structre). Such that child object_id has its parent object_id as ref_object_id.
    e.g.
    Table myTable contains as follows in (object_id, ref_object_id, other_info)

    (1001,1000, 'other_info1')
    (1002,1001, 'other_info2')
    (1003,1002, 'other_info3')
    (1004,1003, 'other_info4')
    ...

    object_id is primary key and unique.
    You see that each row ref_object_id is previous object_id to maintain the parent child kind tree structure.

    Now I want to copy the whole content of myTable to the same myTable with new object_id but maintaing the parent/child tree kind of structure. I am generating object_id through object_seq.nextval.
    So this should be appended to the myTable.

    (1005,1004, 'other_info1')
    (1006,1005, 'other_info2')
    (1007,1006, 'other_info3')
    (1008,1007, 'other_info4')
    ...

    You see that (1005,1004, 'other_info1') is copy from(1001,1000, 'other_info1') with object_id and hence ref_object_id changed and so on.
    If I insert one row at a time then the whole process takes lots of time for entering 100 of such kind of entries. So I want to do the insertion through single query.
    Hope I am able to explain the problem.

    thanx
    fuddoo

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    this will probably do it

    insert into mytable(object_id, ref_object_id, other_info)
    select object_seq.nextval, ref_object_id, other_info
    from mytable

    Alan

  5. #5
    Join Date
    Jan 2005
    Posts
    5
    Thanx for the reply!

    But I think with the following query ref_object_id will remain the old one.

    insert into mytable(object_id, ref_object_id, other_info)
    select object_seq.nextval, ref_object_id, other_info
    from mytable

    (1001,1000, 'other_info1')
    (1002,1001, 'other_info2')
    (1003,1002, 'other_info3')
    (1004,1003, 'other_info4')

    will insert

    (1005,1000, 'other_info1')
    (1006,1001, 'other_info2')
    (1007,1002, 'other_info3')
    (1008,1003, 'other_info4')

    What I want the insert should be

    (1005,1004, 'other_info1')
    (1006,1005, 'other_info2')
    (1007,1006, 'other_info3')
    (1008,1007, 'other_info4')

    ref_object_id should also get changed as the object_id get changed. ref_object_id should always point to the previous row new generated object_id. This is necessary for parent/child structre

    With JDBC batch it is not possible as it doesnot gurantees the order of entries.
    regards,
    Fuddoo

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK you can do it with two statements

    1) create a mapping_table as select object_id, object_seq.nextval newid from mytable;

    2) insert into mytable
    select t1.newid, t2.newid, m.otherinfo
    from mytable m,
    mapping_table t1,
    mapping_table t2
    where m.object_id = t1.object_id
    and m.ref_object_id = t2.object_id

    Check the syntax but you get the idea.

    Alan

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think that should do it :
    Code:
    insert into mytable(object_id, ref_object_id, other_info)
    select object_seq.nextval + 1, object_seq.currval, other_info
    from mytable;
    Tell me if it suits you.

    Alan, what do you think, should that always work ? (I think it should provided object_seq is always incrementing)

    Regards,
    RBARAER
    Last edited by RBARAER; 01-21-05 at 10:59.

  8. #8
    Join Date
    Jan 2005
    Posts
    5
    Many Thanks ! I got the idea ! Now I can implement it!
    regards,
    fuddoo

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    RBAREAR, you cant always assume a sequence will increment by one unless your sure your the only person using the sequence and also the object id values might not always be nicely sequenced as in the example provided. So its usually safer to create a mapping table as above.

  10. #10
    Join Date
    Jan 2005
    Posts
    5
    Hello RBARAER,
    Thanx for the input!
    In my case object_seq is always incrementing by 1.
    But What happens in case of multiuser? I forgot to mention that object_seq.nextval can be run by multi user, for any other table in the application also.
    So we are not sure that we will always get it incremented by 1. That is why my first attempt was to find multiple values entry through single insert .

    rgds,
    Fuddoo

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Yes, you're both right, in multi-user it's not a solution, so IT'S NOT A SOLUTION . Sorry. Thanks for pointing this out. And even if it had worked, I would have preferred your solution Alan : more elegant.

    Regards,

    RBARAER

Posting Permissions

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