How To Create A New Table By Selecting Rows From Another Table In MySQL
Posted by Gokula
11 Jan, 2012
?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 www.GlobalGuideLine.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: 0mysql> SELECT * FROM tipBackup;+----+-------------+-------------------------+-------------+| id | subject | description | create_date |+----+-------------+-------------------------+-------------+| 1 | Learn MySQL | Visit www.GlobalGuideLine.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".
?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 www.GlobalGuideLine.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: 0mysql> SELECT * FROM tipBackup;+----+-------------+-------------------------+-------------+| id | subject | description | create_date |+----+-------------+-------------------------+-------------+| 1 | Learn MySQL | Visit www.GlobalGuideLine.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".
Comments Received:
Please give your suggestions and feedback: