One huge problem with this approach. The new table doesn't have any of the indexes that were present in the previous table. You may be better off to build a script that renames the current tables followed with the full CREATE TABLE statement(s).
Do a SHOW CREATE TABLE current table; Then take that output and put it in a script RENAME TABLE current_table TO new_table_name; CREATE TABLE ..... ON 5.0 you can use PREPARED STATEMENTS if you want to control the new table names. See "13.7. SQL Syntax for Prepared Statements Beginning with MySQL 4.1.3, an alternative interface to prepared statements is available: SQL syntax for prepared statements. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level: " -----Original Message----- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 9:47 AM To: MySQL General Subject: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in subject 2006/1/11, George Law <[EMAIL PROTECTED]>: > Hi All, > > [snip] > > I have to work on an automatic way to rotate these tables every week. > Is there an easy way with SQL to create a new table based on the schema > of an existing table? > I believe CREATE TABLE newtbl SELECT blah... is what you're after : http://dev.mysql.com/doc/refman/5.0/en/create-table.html FTFM : You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
