Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: Duplicating table data

    Hello everybody.

    I've got a table wich have the data for a tree structure, with branchs and dependent branchs.

    Now I need to duplicate an entire structure, and I'm stuck when creating the dependent branchs.

    Ex:
    ID ID_PARENT ORDER CONTENT
    1 NULL 1 branch 1
    2 1 1 branch 2
    3 1 2 branch 3
    4 3 1 branch 4

    and I want to create somenthing like this:

    ID ID_PARENT ORDER CONTENT
    5 NULL 1 branch 1
    6 5 1 branch 2
    7 5 2 branch 3
    8 7 1 branch 4

    Any help?? Many thanks.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well if your just "shifting" the ID value in your copy of the data you can do this

    insert into X
    select ID+4, ID_PARENT+4, ORDER, CONTENT from X

    where 4 could be replaced by the amount you want to shift the ID by.

    Alan

  3. #3
    Join Date
    May 2004
    Posts
    95
    thanks, but I'm not shifting the ID

    the result should be:

    ID ID_PARENT ORDER CONTENT
    1 NULL 1 branch 1
    2 1 1 branch 2
    3 1 2 branch 3
    4 3 1 branch 4
    15 NULL 1 branch 1
    21 15 1 branch 2
    33 15 2 branch 3
    101 33 1 branch 4

    but is not certain that the ID's are sequential, because other user could insert branchs from some other structures.
    Last edited by xixo; 09-02-08 at 08:49.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Maybe you need to better explain how you want to make a copy of the data preferably with an example where the sql I gave earlier wont work.

    Alan

  5. #5
    Join Date
    May 2004
    Posts
    95
    I edited my last post, hope that it will help you better

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    > hope that it will help you better
    I am afraid that it will not help, as you did not reveal the rules for obtaining the new codes.
    Why are they 15, 21, 33, 101?
    Why not 16, 26, 42, 315?
    Why not 17, 31, 56, 538?
    Why not ...?

  7. #7
    Join Date
    May 2004
    Posts
    95
    they are simple inserts for each row
    the problem is that there can be more than 1 command running this copy, so it could happens that the ID's are nor sequential for one command if there is another one doing the same procedure.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Then use an oracle sequence to determine a unique ID number.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    May 2004
    Posts
    95
    Maybe I didn't made myself clear.
    the ID's are sequential, but if someone is inserting rows in that table at the same time that I'm running the copy, the new tree structure will not have a sequential ID, because it could be interrupted by the other insert.

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    In Oracle when your query runs it "sees" the tables as they were at the start of the select. Unlike other databases if someone else is inserting at the same time as you do your select you WONT see their data.

    So I think the insert statement I used earlier will work, you just have to make sure the amount you shift by is big enough that any body inserting at the same wont clash with your new IDs. In most Oracle apps these IDs would be generated by a sequence and after your insert you would increment the sequence to a value greater than the max ID value you just inserted.

    The more complicated alternative to avoiud messing with the sequence would be to create a temp table as a copy of the data. Then add a NEW_ID column and populate that with your sequence. Then write an insert statement to copy the data from the temp table to the original using the NEW_ID instead.

    Alan

Posting Permissions

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