Many times nosotros postulate to exercise backup or re-create of tables inwards database similar MySQL, Oracle or PostgreSQL spell modifying tabular array schema similar adding novel columns, modifying column or dropping columns. Since its ever best to stimulate got a backup of tabular array which tin endure used inwards whatever event. I was looking for an tardily agency to create exact re-create or duplicate tables which must endure same inwards schema every bit good every bit inwards data, similar to creating re-create of folder. Luckily at that spot is an tardily SQL enquiry "CREATE tabular array table_name AS" which allows you lot to exercise exact re-create of tabular array past times executing just 1 SQL query. Yes, you lot read it correctly, no tool is required to exercise backup of tabular array you lot just postulate to execute an SQL query. This is just awesome given its importance in addition to best business office of this SQL enquiry is that it industrial plant inwards near all the database. I stimulate got tested it inwards MySQL in addition to Oracle but t it should piece of occupation perfectly notice inwards other databases similar PostgreSQL, SQL Server in addition to DB2 every bit well. This SQL enquiry tip is inwards continuation of my before SQL enquiry examples similar SQL enquiry to notice duplicate rows inwards a table in addition to SQL enquiry to bring together 3 tables inwards MySQL .
How to re-create tabular array using SQL enquiry inwards MySQL
Now let's come across it an action. In this illustration I am creating exact replica of tabular array for demonstration. We volition locomote a tabular array called AIRCRAFT which has 3 records in addition to later creating backup of AIRCRAFT tabular array nosotros volition verify both count in addition to records to come across if its exact replica of source tabular array or not. Here is our SQL enquiry to exercise backup of tabular array inwards MySQL without whatever tool: create tabular array table_name every bit direct * from source_table
where table_name is elevate of backup tabular array in addition to source_table is elevate of source tabular array inwards database. SELECT enquiry example which is used to fetch information tin endure a complex enquiry which tin fetch information from multiple tabular array every bit well.
-- showing listing of tabular array before creating backup
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| aircraft |
| user |
+----------------+
2 rows IN SET (0.34 sec)
+----------------+
| Tables_in_test |
+----------------+
| aircraft |
| user |
+----------------+
2 rows IN SET (0.34 sec)
-- creating backup of aircraft tabular array past times selecting all data
mysql> CREATE TABLE aircraft_backup AS SELECT * FROM aircraft;
Query OK, 3 rows affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW TABLES;
+-----------------+
| Tables_in_test |
+-----------------+
| aircraft |
| aircraft_backup |
| user |
+-----------------+
3 rows IN SET (0.00 sec)
Query OK, 3 rows affected (0.14 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW TABLES;
+-----------------+
| Tables_in_test |
+-----------------+
| aircraft |
| aircraft_backup |
| user |
+-----------------+
3 rows IN SET (0.00 sec)
-- checking publish of records inwards source tabular array
mysql> SELECT count(*) FROM aircraft;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row IN SET (0.00 sec)
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row IN SET (0.00 sec)
-- verifying publish of records inwards newly created backup table
mysql> SELECT count(*) FROM aircraft_backup;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row IN SET (0.00 sec)
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row IN SET (0.00 sec)
-- information inwards master copy tabular array
mysql> SELECT * FROM aircraft;
+-----+--------+---------------+
| help | aname | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing | 16000 |
| 302 | Airbus | 10000 |
| 303 | Jet | 8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)
+-----+--------+---------------+
| help | aname | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing | 16000 |
| 302 | Airbus | 10000 |
| 303 | Jet | 8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)
-- information inwards backup tabular array should endure precisely same amongst source table
mysql> SELECT * FROM aircraft_backup;
+-----+--------+---------------+
| help | aname | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing | 16000 |
| 302 | Airbus | 10000 |
| 303 | Jet | 8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)
+-----+--------+---------------+
| help | aname | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing | 16000 |
| 302 | Airbus | 10000 |
| 303 | Jet | 8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)
How to exercise tabular array from closed to other tabular array inwards SQL
creating tabular array from closed to other tabular array inwards SQL is same every bit copying tabular array but you lot stimulate got a selection to either just re-create the schema or re-create schema in addition to information together. In lodge to exercise SQL tabular array from closed to other tabular array just locomote next exercise tabular array SQL enquiry in addition to supersede elevate of tabular array amongst exact elevate you lot want.
create tabular array destination_table every bit direct * from source_table;
In lodge to exercise tabular array past times copying schema from closed to other tabular array amongst out information locomote a status inwards WHERE clause which ever returns false.
mysql> CREATE TABLE AIRCRAFT_SCHEMA_BACKUP AS SELECT * FROM AIRCRAFT WHERE 3=4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM AIRCRAFT_SCHEMA_BACKUP;
Empty SET (0.00 sec)
mysql> DESCRIBE AIRCRAFT_SCHEMA_BACKUP;
+---------------+-------------+------+-----+---------+-------+
| FIELD | Type | NULL | KEY | DEFAULT | Extra |
+---------------+-------------+------+-----+---------+-------+
| help | int(11) | NO | | NULL | |
| aname | varchar(20) | YES | | NULL | |
| cruisingrange | int(11) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows IN SET (0.06 sec)
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM AIRCRAFT_SCHEMA_BACKUP;
Empty SET (0.00 sec)
mysql> DESCRIBE AIRCRAFT_SCHEMA_BACKUP;
+---------------+-------------+------+-----+---------+-------+
| FIELD | Type | NULL | KEY | DEFAULT | Extra |
+---------------+-------------+------+-----+---------+-------+
| help | int(11) | NO | | NULL | |
| aname | varchar(20) | YES | | NULL | |
| cruisingrange | int(11) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows IN SET (0.06 sec)
f you lot desire to creat a tabular array from closed to other tabular array amongst information in addition to schema than just execute to a higher house SQL enquiry without WHERE clause.
In illustration if you lot don't desire your novel tabular array to contains all columns in addition to entirely few columns from the master copy tabular array than instead of using select * just locomote direct column, column etc every bit shown inwards below SQL query:
mysql> CREATE TABLE AIRCRAFT_BK AS SELECT aid, aname FROM AIRCRAFT;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM AIRCRAFT_BK;
+-----+--------+
| help | aname |
+-----+--------+
| 301 | Boeing |
| 302 | Airbus |
| 303 | Jet |
+-----+--------+
3 rows IN SET (0.00 sec)
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM AIRCRAFT_BK;
+-----+--------+
| help | aname |
+-----+--------+
| 301 | Boeing |
| 302 | Airbus |
| 303 | Jet |
+-----+--------+
3 rows IN SET (0.00 sec)
That's all on creating backup of tabular array or copying tabular array past times using SQL query. We stimulate got seen how to re-create tables, how to exercise tabular array from closed to other tabular array amongst information in addition to without information in addition to how to exercise duplicates of table. You ever stimulate got flexibility on choosing columns or data.
