copying tables from 1 database to another

[raindropz12]

Is it possible to copy tables from 1 database to another database?

Thanks in advanced.

[trq]

Yes.

[raindropz12]

Yes.

can you please tell me how?

[QuickOldCar]

There are ways to import the tables into new tables.

 

Here's the easiest method without taking a long time or running into time and memory limits.

Method 1:

Browse to your MYSQL folder, then data folder, then databasename folder.

copy the files associated to that table, rename them and place them back into the database folder

 

example:

MySQL\data\databasename

 

It will contain 3 files for each table name, copy and rename them

table_name.frm

table_name.MYD

table_name.MYI

 

to something like below or what you want

table_name_copy.frm

table_name_copy.MYD

table_name_copy.MYI

 

After you rename and place back the files, do a REPAIR on the table and will have an exact clone.

 

Method 2:

export your table into a .sql file, create a new table and import the .sql back in, but you have to edit the .sql file to have the proper table name first.

 

Note: if you include the "create table" function in the sql export file, is no need to manually create the new table

 

Method 3:

Otherwise you can do it the long way if you have a very large table

 

SELECT *

INTO table_name_copy

FROM table_name;

 

If I remember correctly this method will not retain your index information.

[QuickOldCar]

I realized you said database to database

 

Copy and rename the database folder itself, place new copy into MYSQL\data, then do the repair.

[raindropz12]

how about copying records from 1 table to another table? thanks.

[fenway]

how about copying records from 1 table to another table? thanks.

Best way is to use the mysql commands directly, if you can connect to both with the same user.

[raindropz12]

how about copying records from 1 table to another table? thanks.

Best way is to use the mysql commands directly, if you can connect to both with the same user.

what are the mysql commands to copy records from 1 table to another table? please give examples, thanks.

[fenway]

INSERT INTO...SELECT WHERE...