Mysql insert with loop out of select statement

Question

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 ;

Bill Karwin · Accepted Answer · 2022-04-27 19:03:55Z

2

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

answered Apr 27, 2022 at 19:03

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lenny Over a year ago

It worked, I also had to add a constant value but did that with: INSERT INTO users(student_id, university_id) SELECT s.id, '1' FROM students s

Collectives™ on Stack Overflow

Mysql insert with loop out of select statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related