'Select' and 'insert into' multiple rows by a My Sql event

Question

I need to write a MySql Event to select some values from a table under some conditions and put those values in a second table. By the select statement. I get multiple rows, so I need to store data in the second table as a batch. How can I achieve this? I wrote an event to select one row. But what I need to do is select multiple rows and store as a batch.

The event I wrote is as below.

DELIMITER $$
  CREATE EVENT salary_add
  ON SCHEDULE EVERY 24 HOUR
  DO
  BEGIN
  DECLARE month_end DATETIME;
  DECLARE today DATETIME;
  DECLARE reg_id VARCHAR(6);
  DECLARE sal INT(8);
  SET month_end = LAST_DAY(DATE(NOW()));
  SET today = DATE(NOW());

   IF month_end=today THEN
   SELECT register_id,salary INTO reg_id,sal FROM employees 
   WHERE status ='1' LIMIT 1;
   INSERT INTO tbl_salary (register_id,amount,salary_date,status) VALUES (reg_id,sal,today,'0');
   END IF;
   END $$
DELIMITER ;

score 1 · Accepted Answer · answered Sep 12 '18 at 08:14

You can insert selected rows into the target table at once. For example:

DELIMITER $$
  CREATE EVENT salary_add
  ON SCHEDULE EVERY 24 HOUR
  DO
  BEGIN
    DECLARE month_end DATETIME;
    SET month_end = LAST_DAY(CURDATE());

    IF month_end=CURDATE() THEN   
      INSERT INTO tbl_salary (register_id, amount, salary_date, status)
      SELECT register_id,
             salary,
             CURDATE(),
             '0'
      FROM employees
      WHERE status ='1'
    END IF;
  END $$
DELIMITER ;

'Select' and 'insert into' multiple rows by a My Sql event

1 Answers1