copy table from one database to another

[alaios]

Hello i have two databases in mysql server
How i can copy one table from one database to the other one?

[Tangerine Dream]

Hi, to "copy" tables between databases on the same MySQL server, you can specify LIKE keyword for CREATE TABLE statement and then use INSERT SELECT (http://dev.mysql.com/doc/mysql/en/insert-select.html) statement the way like this:

USE db2;

CREATE TABLE table2 LIKE db1.table1;

INSERT INTO table2
SELECT * FROM db1.table1;

Note: you don't need to create indexes for the new table since like LIKE keyword also "copies" indexes of the old table. To copy between different MySQL servers, you can use dump file, i.e set of DDL (to create table and indexes) & DML (to insert data) statements. Check 8.8. The mysqldump Database Backup Program (http://dev.mysql.com/doc/mysql/en/mysqldump.html). BTW if you need to copy many tables, you can also use dump file for the same MySQL server

[alaios]

mysql> CREATE TABLE Titles LIKE ecommerce.Titles;
ERROR 1064: You have an error in your SQL syntax near 'LIKE ecommerce.Titles' at line 1

[Tangerine Dream]

Yes, LIKE keyword supported by MySQL 4.1 or higher version:
In MySQL 4.1, you can also use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes the original table has:

CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE ... LIKE does not copy any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.

[alaios]

...what can i do now?

[root@skia root]# mysql --version
mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)

[Tangerine Dream]

You should use old good dump file to copy table structure & rows :)