How To Create a New Table by Selecting Rows from Another Table in MySQL?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:

mysql> INSERT INTO tip VALUES (1, 'Learn MySQL', 
   'Visit dev.fyicenter.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)

mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject     | description             | create_date |
+----+-------------+-------------------------+-------------+
|  1 | Learn MySQL | Visit dev.fyicenter.com | 2006-07-01  |
+----+-------------+-------------------------+-------------+
1 row in set (0.00 sec)

As you can see, this SQL script created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

⇒ Add a New Column to an Existing Table in MySQL

⇐ Show CREATE TABLE Statements of Existing Tables in MySQL

⇑ CREATE, ALTER and DROP Statements in MySQL

⇑⇑ MySQL Database Tutorials