Mysql insert with loop out of select statement-CodePudding

I have a query which lists users IDs based on some criteria.

Now I want to insert every user ID into different table. I was thinking of a loop which would go through the list and insert a row one by one.

This is my select query:

SELECT s.id, s.name FROM students s
limit 10000;

How can I make a loop out of that. How to get to the values out of select statement in the loop?

DROP PROCEDURE IF EXISTS loop_date;
DELIMITER //  
CREATE PROCEDURE loop_date()
BEGIN
DECLARE i INT DEFAULT 1; 
WHILE (number_of_students < 10000) DO
INSERT INTO users (student_id,student_name) VALUES (X?, Y?);
SET i = i 1;
END WHILE;
END;
//
DELIMITER ;

CodePudding user response：

You can INSERT ... SELECT in a single statement, instead of INSERT ... VALUES.

INSERT INTO users (student_id,student_name)
SELECT s.id, s.name FROM students s
limit 10000;

See https://dev.mysql.com/doc/refman/8.0/en/insert-select.html