Copy a table ?

[Stinger51]

I don't see anything in the MySQL manual about how to do this. It should be a simple command from the monitor, no? Or does such a command even exist? I'm using ver. 3.22.

[mattotto77]

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

thats if the new table doesnt exist;

[mourisj]

If you have access to the datafiles (write permissions on files and folders), you can simply copy the appropriate files and rename them to what you wnt to have as table-name. Don't forget the index files, etc.
(In windows f.e. in the Windows Explorer, on Linux on the command line, ...)

[Stinger51]

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

gave me a syntax error. Not sure why. But thanks for the suggestion.

[Stinger51]

I'm using Win98 SE and my MySQL folder looks like :

MySQL
- data
- - testDB

testDB is the DB name here. I had three files with the following extensions :

MyTable.frm
MyTable.isd
MyTable.ism

So I copied these 3 and then renamed them to what I wanted. So far, so good. Not sure, but I think the .frm one (which is a VB form) is for MySQL Front. Anyway, MySQL Front is seeing the new tables.

These are very small files and I can easily back them up to a floppy periodically for safe keeping.

Thanks for you input. Comments are welcomed.

[mourisj]

It definitely works fine, so check for a syntax error !!

Current database: test

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| person |
| societe |
+----------------+
2 rows in set (0.00 sec)

mysql> select count() from person;
+----------+
| count() |
+----------+
| 268 |
+----------+
1 row in set (0.06 sec)

mysql> create table new_tbl select * from person;
Query OK, 268 rows affected (0.10 sec)
Records: 268 Duplicates: 0 Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| new_tbl |
| person |
| societe |
+----------------+
3 rows in set (0.00 sec)

[Stinger51]

I'm using MySQL ver. 3.22.34-shareware

When I try :

mysql> create table new_table select * from other_table;

I still get this error :

ERROR 1064: You have an error in your SQL syntax near 'select * from other_table' at line 1

😕

[Rodders]

probably because you don't have a table called 'other_table' ??

replace that with the name of the table you are trying to copy

[Stinger51]

The table "other_table" does indeed exist. I verified that with :

myslq> show tables;

[mourisj]

What about reserved words ?

Make sure your table-names are not reserved words in MySql . E.g., it is a bad idea to call a table TABLE, or to call a field DATE!

One of these might cause you trouble !!

Regards,

JJ Mouris

[Rodders]

Sorry........holding head in shame

[Stinger51]

Good point! But that's not the problem. I did, however, discover that the hard way one time. I had a field named the same as a reserved word (can't remember which now) and it did cause me a lot of grief! A LOT of grief.

TO HIM THAT HATH EARS TO HEAR, do not name a field the same as a reserved word.

My tables involved are not reserved words, far as I know :

mysql> create table business_stats select * from agency_stats;

I just think my MySQL installation is just fuked in some ways, even tho I've installed it about 3 times now. And Win 98SE ain't helping matters. I had a crash a few weeks ago using Win Me, and rolled everything back to 98. And now things seem to be even worse. :bemused:

Another long standing problem I've had that I've never resolved is, I can only work with one local database. PHP 4.3 simply refuses to select any other DB than the one I set up originally called "testDB." It will open a connection to another DB, but will not select it. Very weird, I know. I've given up on that one.

[mourisj]

You should definitely try to use a REAL OS ;-)))))