Search code examples
mysqlsqldatabasecreate-table

Mysql create tables like other tables

I have 5 databases(or schemas) which have more than 100 tables in MySQL and now I need to create new database which have tables like older tables. New tables' names are in this format

"OldDatabaseName"_"OldTableName"_History

and their columns are almost same, just I need to add 2 new columns like START and END timestamps for every table.

There won't be any foreign keys in new tables, and also not any auto increment column, just ID and START columns will be unique key.

So, What kind of SQL do I need to use for this purpose?

Solution

  • "Are there any loop through solution for this?" Yes.

    write a stored procedure and:

    • Declare Variables, Cursor and Exception_Handler (we use the Exception_Handler to find an exit point for our loop)

    • load all schema_names/table_names from information_schema.tables that
      should be duplicated into the cursor (the cursor allows us to fetch the
      data into variables so we can use them in our statements).

    • after that you can use the schema_names/table_names in a loop to create a copy of every table in the new schema. just fetch the schema_name and table_name into variables and use prepared statements to create your tables. with every run-through a table will be created from inside the loop. Once all tables have been created an exception will occur to tell you that there is no more data.

    as people have pointed out:

    • empty tables without keys :

      create table new_schema_name.new_table_name 
select * from old_schema_name.old_table_name limit 0;

    • filled tables without keys (includes all data of the original table) :

      create table new_schema_name.new_table_name
select * from old_schema_name.old_table_name;

    • empty table with all properties of the original table (in case its needed):

      create table new_schema_name.new_table_name 
like old_schema_name.old_table_name ;

    in case you have small tables (<5 million records [also depends on hardware]) and you don't want to repeat this job, you can use a GUI to copy the tables by drag and drop. Navicat for MySQL supports this function. after that you can use a texteditor + excel to build your alter commands to adjust the table names and add the new columns. the list of table_names for that can be selected from information_schema.tables. this will take much more time than running the procedure but should still be faster for beginners compared to writing and testing the procedure.

    i recommend the stored procedure approach