CODEWITHSUNDEEP
mysqldatabase
leo
leo

Reputation: 583

How to create tables with same table structure from an existed one in mysql?

How can I automatically create say 100 tables from an existed table. Tables names would like to be table1, table2, table3...table100. I don't want to execute sql:"create bable table2 like table1" 100 times. Thanks in advance.

Upvotes: 0

Views: 302

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can create a stored procedure and call it once. Here is an example of creating 5 table with name test1...test5 from a table test

Change the loop value and table name in the below procedure

delimiter //
create procedure `create_tables`()
begin
 declare x  INT ;
 set x = 1 ;
 while x  <= 5 do
   set  @qry = concat("create table test",x," like test ");
   prepare stmt from @qry;
   execute stmt ;
   deallocate prepare stmt ;  
   set  x = x + 1;
  end while;
end ;//

delimiter ;

Finally call the procedure as

call create_tables();

Then run the command to see the tables 

show tables;

Here is a test case in mysql cli

mysql> delimiter //
mysql> create procedure `create_tables`()
    -> begin
    ->  declare x  INT ;
    ->  set x = 1 ;
    ->  while x  <= 5 do
    ->    set  @qry = concat("create table test",x," like test ");
    ->    prepare stmt from @qry;
    ->    execute stmt ;
    ->    deallocate prepare stmt ;  
    ->    set  x = x + 1;
    ->   end while;
    -> end ;//
Query OK, 0 rows affected (0.22 sec)
mysql> delimiter ;
mysql> call create_tables() ;
Query OK, 0 rows affected (0.74 sec)

mysql> show tables ;
+-----------------------+
| Tables_in_test        |
+-----------------------+
| test                  |
| test1                 |
| test2                 |
| test3                 |
| test4                 |
| test5                 |
+-----------------------+

Upvotes: 1

Related Questions