-
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.
-
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.
-
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
-
-
Uh?
I'd vote for:
INSERT INTO second_table (creditor, sumofcost)
VALUES(r_sumcost.creditor, r_sumcost.sumofcost);
-
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.
-
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?
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|