Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Together With Postgresql Database

Many times nosotros take away to gain backup or re-create of tables inwards database similar MySQL, Oracle or PostgreSQL piece modifying tabular array schema similar adding novel columns, modifying column or dropping columns. Since its ever best to cause got a backup of tabular array which tin live on used inwards whatever event. I was looking for an slow agency to create exact re-create or duplicate tables which must live on same inwards schema equally good equally inwards data, similar to creating re-create of folder. Luckily in that place is an slow SQL inquiry "CREATE tabular array table_name AS" which allows you lot to gain exact re-create of tabular array yesteryear executing just i SQL query. Yes, you lot read it correctly, no tool is required to gain backup of tabular array you lot just take away to execute an SQL query. This is just awesome given its importance as well as best business office of this SQL inquiry is that it industrial plant inwards most all the database. I cause got tested it inwards MySQL as well as Oracle but t it should operate perfectly detect inwards other databases similar PostgreSQL, SQL Server as well as DB2 equally well. This SQL inquiry tip is inwards continuation of my before SQL inquiry examples similar SQL inquiry to detect duplicate rows inwards a table as well as SQL inquiry to bring together iii tables inwards MySQL .

How to re-create tabular array using SQL inquiry inwards MySQL

Now let's encounter it an action. In this instance I am creating exact replica of tabular array for demonstration. We volition purpose a tabular array called AIRCRAFT which has 3 records as well as later creating backup of AIRCRAFT tabular array nosotros volition verify both count as well as records to encounter if its exact replica of source tabular array or not. Here is our  SQL inquiry to gain backup of tabular array inwards MySQL without whatever tool:

create tabular array table_name equally lead * from source_table

where table_name is lift of backup tabular array as well as source_table is lift of source tabular array inwards database. SELECT inquiry example which is used to fetch information tin live on a complex inquiry which tin fetch information from multiple tabular array equally well.

-- showing listing of tabular array before creating backup

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| aircraft       |
| user           |
+----------------+
2 rows IN SET (0.34 sec)

-- creating backup of aircraft tabular array yesteryear 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)

-- checking discover of records inwards source tabular array

mysql> SELECT count(*) FROM aircraft;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row IN SET (0.00 sec)

-- verifying discover of records inwards newly created backup table

mysql> SELECT count(*) FROM aircraft_backup;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row IN SET (0.00 sec)

-- information inwards master copy tabular array

mysql> SELECT * FROM aircraft;
+-----+--------+---------------+
| assist | aname  | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing |         16000 |
| 302 | Airbus |         10000 |
| 303 | Jet    |          8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)

-- information inwards backup tabular array should live on just same alongside source table

mysql> SELECT * FROM aircraft_backup;
+-----+--------+---------------+
| assist | aname  | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing |         16000 |
| 302 | Airbus |         10000 |
| 303 | Jet    |          8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)

How to gain tabular array from just about other tabular array inwards SQL

creating tabular array from just about other tabular array inwards SQL  is same equally copying tabular array but you lot cause got a selection to either just re-create the schema or re-create schema as well as information together. In gild to gain SQL tabular array from just about other tabular array just purpose next gain tabular array SQL inquiry as well as supplant lift of tabular array alongside exact lift you lot want.

create tabular array destination_table equally lead * from source_table;

In gild to gain tabular array yesteryear copying schema from just about other tabular array alongside out information purpose 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 |
+---------------+-------------+------+-----+---------+-------+
| assist           | 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 just about other tabular array alongside information as well as schema than just execute inwards a higher house SQL inquiry without WHERE clause.

In instance if you lot don't desire your novel tabular array to contains all columns as well as solely few columns from the master copy tabular array than instead of using select * just purpose lead column, column etc equally 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;
+-----+--------+
| assist | 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 yesteryear using SQL query. We cause got seen how to re-create tables, how to gain tabular array from just about other tabular array alongside information as well as without information as well as how to gain duplicates of table. You ever cause got flexibility on choosing columns or data.

Further Learning
How to gain out transaction inwards database – MySQL

Difference betwixt truncate as well as delete inwards SQL

How to cause got backup of database using Mysqldump utility inwards MySQL

10 MySQL dominance Example

Difference betwixt clustered as well as nonclustered index inwards SQL