Insert results of a stored procedure into an existing table

Question

I have a table called Percentages with a Column called 5StarResults, I would like to populate this with the results of a stored procedure names 5star.

When I use 'Call 5star' it returns just a percentage, I would like this percentage inserted into the Percentages table.

Please can you assist, I have tried to edit the procedure to include an Insert but it always returns saying 0 rows inserted.

Thank you

edit,

Stored Procedure is as follows

BEGIN
  declare Rating5Total int default 5;
  declare Rating5Win int default 5;
  declare 5StarPerformance decimal;
  set Rating5Total = (select COUNT(Ratings) from vHighPerformance where Ratings = 7);
  set Rating5Win = (select COUNT(Results) from vHighPerformance where Ratings = 7 and Results = 1);
  set 5StarPerformance = Rating5Win*100.0/Rating5Total;
  Select 5StarPerformance;
END

you'r going to need to use a temp table, let me try to mock something up — CSharper, Oct 11 '13 at 19:18

wchiquito · Accepted Answer · 2013-10-11T22:48:45.937

If an option is to do the INSERT in the same stored procedure, the following code should work:

CREATE PROCEDURE `5star`()
BEGIN
    DECLARE Rating5Total INT DEFAULT 5;
    DECLARE Rating5Win INT DEFAULT 5;
    DECLARE 5StarPerformance DECIMAL(18, 3);
    SET Rating5Total := (SELECT COUNT(Ratings) FROM vHighPerformance WHERE Ratings = 7);
    SET Rating5Win := (SELECT COUNT(Results) FROM vHighPerformance WHERE Ratings = 7 AND Results = 1);
    SET 5StarPerformance := Rating5Win*100.0/Rating5Total;
    /* INSERT INTO percentages (percentage) VALUES (5StarPerformance); */
    INSERT INTO percentages (id, percentage) VALUES (1, 5StarPerformance)
       ON DUPLICATE KEY UPDATE percentage = 5StarPerformance;
    SELECT 5StarPerformance;
END$$

An example can be seen in SQL Fiddle.

UPDATE

If you need one single record on table percentages, you can make a UPSERT. See the updated code.

Is it possible with an UPDATE as every time it runs it adds a new row. thanks — user2785062, Oct 11 '13 at 20:21

Insert results of a stored procedure into an existing table

1 Answers1