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
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.
Table myTable contains as follows in (object_id, ref_object_id, other_info)
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.
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.
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.
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 .
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.