Populating a table.....

[Alchemy]

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?

[gandolf989]

This would probably be the most efficient way.

INSERT INTO second_table (creditor, sumofcost)
SELECT creditor, SUM(cost) 
  FROM table group by creditor;

COMMIT;

But you could also do:

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.

[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

[gandolf989]

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!

[DaPi]

Uh?

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

[gandolf989]

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.

[DaPi]

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?

[Alchemy]

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.

[gandolf989]

Originally posted by DaPi

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.

[Alchemy]

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.