Looping through various tables MySQL

Question

I have a few similarly named tables in MySQL server (e.g: table_1; table_2; table_3 etc.). What I want to do is loop through these tables using a dynamic select query to insert the data into another table, let's say summary_table. Usually what I would do in Python if I create a connection to MySQL server:

for i in range(1,10):
    insert = 'Insert into summary_table(*some column names*)'
    insert += 'Select (*similar column names*) from table_' + str(i) + ';'

However, I'm not very familiar with the declare and syntax in MySQL server. I tried using this:

drop procedure if exists insert_loop;
delimiter //
create procedure insert_loop()
deterministic
begin
    declare i int default 1;
    declare tablename varchar(50);
    set tablename := concat('table_',i);
    while i <= 10 do
        insert into summary_table(*some column names*)
        select *some column names* from tablename;
        set i = i+1;
    end while;
end //
delimiter ;
call insert_loop;

But the error returns as schema_name.tablename doesn't exist. For more information, I'm using MySQL community server version 8.0.23 at the moment for my learning purpose. Any feedback or help how I can solve this error with MySQL is appreciated. Thank you.

score 0 · Accepted Answer · edited Feb 02 '21 at 14:14

you can use EXECUTE command:

drop procedure if exists insert_loop;
    delimiter //
    create procedure insert_loop()
    deterministic
    begin
        declare i int default 1;
        declare tablename varchar(50);
        set tablename := concat('table_',i);
        while i <= 3 do
           set tablename := concat('table_',i);
           SET @query = concat('insert into summary_table (id, name, phone) select id, name , phone from ', tablename);
           SET @result = NULL;
           PREPARE stmt1 FROM @query;
           EXECUTE stmt1;
           set i = i+1;
        end while;
    end //
    delimiter ;

    call insert_loop;

Looping through various tables MySQL

1 Answers1