Populating a table.....
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Populating a table.....

  1. #1
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    392

    Populating a table.....

    I'm placing the question here because it really isn't pressing to accomplish the task I have in mind.

    First, I'm not much of a programmer, second, I have taken time to research my problem.

    My problem is this: I have this itching sensation on my backsi.....ooops, wrong forum...lol

    In all seriousness. I would like to populate a table with data from another table. I have a table that is stuctured like this:

    creditor varchar2(30),
    service date,
    cost number(5),
    paid number date));

    I would like to populate a second table that would look like this:
    creditor varchar2(30),
    sumOfCost number(5));

    Th sql I would like to use is select creditor, sum(cost) from table group by creditor.

    What pl/sql would accomplish this?
    Rick

    Sigh.....those were the days my friend, I thought they'd never end.
    I too remember when this place was coo.


  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    This would probably be the most efficient way.

    Code:
    INSERT INTO second_table (creditor, sumofcost)
    SELECT creditor, SUM(cost) 
      FROM table group by creditor;
    
    COMMIT;
    But you could also do:

    Code:
    DECLARE
       CURSOR c_sumcost IS
          SELECT creditor, SUM(cost) sumofcost
            FROM table group by creditor;
    BEGIN
       FOR r_sumcost IN c_sumcost
       LOOP
          INSERT INTO second_table (creditor, sumofcost)
          SELECT r_sumcost.creditor, r_sumcost.sumofcost
            FROM table group by creditor;
       END LOOP;
       COMMIT;
    END;
    /
    You can also convert this to use a PL/SQL collection.
    But if the table isn't very big use the first method.
    Last edited by gandolf989; 12-08-2004 at 03:28 PM.
    this space intentionally left blank

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Gandolf, honorable e-colleague, I like your simple insert statement. I think you should have stopped while you were winning . . .

    . . . as for the pl/sql, wtf? I guess copy-paste ran away with itself

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Originally posted by DaPi
    Gandolf, honorable e-colleague, I like your simple insert statement. I think you should have stopped while you were winning . . .

    . . . as for the pl/sql, wtf? I guess copy-paste ran away with itself
    Alchemy did ask for a PL/SQl solution. btw, are you critiquing me for summing a summed column? Thanks to your keen eye, I have resolved that issue.

    Just remember, you can always do it better with PL/SQL!
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Uh?

    I'd vote for:
    INSERT INTO second_table (creditor, sumofcost)
    VALUES(r_sumcost.creditor, r_sumcost.sumofcost);

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Originally posted by DaPi
    Uh?

    I'd vote for:
    INSERT INTO second_table (creditor, sumofcost)
    VALUES(r_sumcost.creditor, r_sumcost.sumofcost);
    For me it depends on the amount of data, and the frequency that it gets moved. If its a one time thins and direct insert is an option, then it will be much faster, otherwise PL/SQL can be used.
    this space intentionally left blank

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    DECLARE
       CURSOR c_sumcost IS
          SELECT creditor, SUM(cost) sumofcost
            FROM table group by creditor;
    BEGIN
       FOR r_sumcost IN c_sumcost
       LOOP
          INSERT INTO second_table (creditor, sumofcost)
          VALUES (r_sumcost.creditor, r_sumcost.sumofcost);
       END LOOP;
       COMMIT;
    END;
    /
    Now do you see what I mean?

  8. #8
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    392
    Thanks alot gandol989 and DaPi. Appreciate the response. The table I have has, only, 110 records right now. I do not expect much growth, maybe double, then, the table will no longer be needed. I will go with the insert option because of the size.

    From my researching for this I did come across numerous options using the loop, but because of my lack of understanding I did not want to do something that would require putting a new table up though that would not be all that disastrous.

    Thanks again.
    Rick

    Sigh.....those were the days my friend, I thought they'd never end.
    I too remember when this place was coo.


  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Originally posted by DaPi
    Code:
    DECLARE
       CURSOR c_sumcost IS
          SELECT creditor, SUM(cost) sumofcost
            FROM table group by creditor;
    BEGIN
       FOR r_sumcost IN c_sumcost
       LOOP
          INSERT INTO second_table (creditor, sumofcost)
          VALUES (r_sumcost.creditor, r_sumcost.sumofcost);
       END LOOP;
       COMMIT;
    END;
    /
    Now do you see what I mean?
    So you want working code? I'm not sure how I missed something that obvious.
    this space intentionally left blank

  10. #10
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    392
    You know, I will fool around with both, just to get it down right then, I will not have to ask that question again, which would have likely happened.
    Rick

    Sigh.....those were the days my friend, I thought they'd never end.
    I too remember when this place was coo.


Posting Permissions

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


Click Here to Expand Forum to Full Width