Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Creating New Tables with SELECT Statements

By: FYIcenter.com

(Continued from previous topic...)

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

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".

(Continued on next topic...)

1. What Are DDL Statements?
2. How To Create a New Table?
3. What Happens If You No CREATE Privilege in a Database?
4. How To Get a List of All Tables in a Database?
5. How To Get a List of Columns in an Existing Table?
6. How To See the CREATE TABLE Statement of an Existing Table?
7. How To Create a New Table by Selecting Rows from Another Table?
8. How To Add a New Column to an Existing Table?
9. How To Delete an Existing Column in a Table?
10. How To Rename an Existing Column in a Table?
11. How To Rename an Existing Table?
12. How To Drop an Existing Table?
13. How To Create an Index for a Given Table?
14. How To Get a List of Indexes of a Given Table?
15. How To Drop an Existing Index?
16. How To Create a New View?
17. How To Drop an Existing View?