How To Create a New Table by Selecting Rows from Another Table in MySQL?
Submitted by: AdministratorLet'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: 0
<pre>mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+</pre>
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".
Submitted by: Administrator
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: 0
<pre>mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+</pre>
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".
Submitted by: Administrator
Read Online MySQL Programming Job Interview Questions And Answers
Top MySQL Programming Questions
☺ | How To Use CASE Expression? |
☺ | How To Use Regular Expression in Pattern Match Conditions? |
☺ | What Is TIMESTAMP in MySQL? |
☺ | What Is ISAM? |
☺ | How To Delete an Existing Column in a Table? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |