[SOLVED] Inserting Multiple Rows with one Stored Producer

Inserting Multiple Rows with one Stored Producer

Good Day.

I'm working on the Stored Producer of MySql. is it possible to insert multiple rows in a single Stored Producer, spinsertmultiple(pname, pdateTimeFrom, pdateTimeTo)

CREATE DEFINER=`root`@`localhost`PROCEDURE `spinsertmultiple`
( pname varchar(32), pdateTimeFrom DATETIME, pdateTimeTo DATETIME)
BEGIN
    INSERT INTO vesseleventsactual(
    name, time, date
    )
    VALUES(
    pname, ??, ??
    );
END$$
DELIMITER ;

I don't have the right codes what should I write in my values. to add multiple rows.

Example:

CALL spinsertmultiple('name', '2015-08-17 8:00:00', '2015-08-17 12:00:00');

Expected Output in my table (Columns in my table: name | time | date)

name | 8:00:00 | 2015-08-17
name | 9:00:00 | 2015-08-17
name | 10:00:00 | 2015-08-17
name | 11:00:00 | 2015-08-17
name | 12:00:00 | 2015-08-17

I've been doing some research but all solutions are using INSERT INTO tablename (colname ...) VALUES (values ...), (values ...), (values ...)

Solution

Yes, one possible solution would be using a loop. You subtract the difference of your start and end times, and use that for your counter.

Semi pseudo-code / SQL:

SET @inserts = 1
SET @insertsNeeded = @EndTime.Hours - @StartTime.Hours

WHILE(@inserts <= @insertsNeeded)
    INSERT INTO tablename(...)
    @inserts = @inserts + 1