Mysql create tables like other tables

Question

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?

Lesson 1: Have a script file for each database/schema to be able to re-create or create copy db. — jarlh
– jarlh, Commented Apr 19, 2016 at 12:31

iLikeMySql · Accepted Answer · 2016-04-19 15:06:28Z

"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

JYoThI · Accepted Answer · 2016-04-19 12:36:33Z

1

first create the table using this one

create table new_table select * from old_table;

and then use alter table query to alter your wish

answered Apr 19, 2016 at 12:36

JYoThI

12.1k1 gold badge13 silver badges26 bronze badges

3 Comments

kadir Over a year ago

Are there any loop through solution for this? I mean like get all schemas and for each table in those schemas run this line of sql?

Preuk Over a year ago

Careful, thiis won't duplicate PK, FK and indexes from original table (at least with MyISAM)

kadir Over a year ago

@Preuk actually that is exactly what I want, thanks for warning

Preuk · Accepted Answer · 2016-04-19 13:01:07Z

1

Given recent comments, the best answer seems to be to dump the table using mysqldump, modify the table name in the output before committing the CREATE TABLEand INSERT again. Then you can ALTER TABLE and UPDATE this new tablea to your liking.

edited Apr 19, 2016 at 13:01

answered Apr 19, 2016 at 12:51

Preuk

6328 silver badges19 bronze badges

Collectives™ on Stack Overflow

Mysql create tables like other tables

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related