Sql Enquiry To Copy, Duplicate Or Backup Tabular Array Inwards Mysql, Oracle Together With Postgresql Database

Many times nosotros require to practise backup or re-create of tables inward 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 cause got a backup of tabular array which tin dismiss live on used inward whatsoever event. I was looking for an slow means to create exact re-create or duplicate tables which must live on same inward schema equally good equally inward data, similar to creating re-create of folder. Luckily in that place is an slow SQL enquiry "CREATE tabular array table_name AS" which allows y'all to practise exact re-create of tabular array yesteryear executing just ane SQL query. Yes, y'all read it correctly, no tool is required to practise backup of tabular array y'all just require to execute an SQL query. This is just awesome given its importance too best business office of this SQL enquiry is that it industrial plant inward nearly all the database. I cause got tested it inward MySQL too Oracle but t it should move perfectly discovery inward other databases similar PostgreSQL, SQL Server too DB2 equally well. This SQL enquiry tip is inward continuation of my before SQL enquiry examples similar SQL enquiry to discovery duplicate rows inward a table too SQL enquiry to bring together 3 tables inward MySQL .

How to re-create tabular array using SQL enquiry inward MySQL

Now let's come across it an action. In this instance I am creating exact replica of tabular array for demonstration. We volition role a tabular array called AIRCRAFT which has 3 records too afterward creating backup of AIRCRAFT tabular array nosotros volition verify both count too records to come across if its exact replica of source tabular array or not. Here is our  SQL enquiry to practise backup of tabular array inward MySQL without whatsoever tool:

create tabular array table_name equally direct * from source_table

where table_name is advert of backup tabular array too source_table is advert of source tabular array inward database. SELECT enquiry example which is used to fetch information tin dismiss live on a complex enquiry which tin dismiss 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 give away of records inward source tabular array

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

-- verifying give away of records inward newly created backup table

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

-- information inward master tabular array

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

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

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

How to practise tabular array from or too thence other tabular array inward SQL

creating tabular array from or too thence other tabular array inward SQL  is same equally copying tabular array but y'all cause got a selection to either just re-create the schema or re-create schema too information together. In gild to practise SQL tabular array from or too thence other tabular array just role next practise tabular array SQL enquiry too supervene upon advert of tabular array alongside exact advert y'all want.

create tabular array destination_table equally direct * from source_table;

In gild to practise tabular array yesteryear copying schema from or too thence other tabular array alongside out information role a status inward 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 |
+---------------+-------------+------+-----+---------+-------+
| assistance           | int(11)     | NO   |     | NULL    |       |
| aname         | varchar(20) | YES  |     | NULL    |       |
| cruisingrange | int(11)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows IN SET (0.06 sec)

f y'all desire to creat a tabular array from or too thence other tabular array alongside information too schema than just execute higher upwardly SQL enquiry without WHERE clause.

In instance if y'all don't desire your novel tabular array to contains all columns too entirely few columns from the master tabular array than instead of using select * just role direct column, column etc equally shown inward 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;
+-----+--------+
| assistance | 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 practise tabular array from or too thence other tabular array alongside information too without information too how to practise duplicates of table. You ever cause got flexibility on choosing columns or data.

Further Learning
How to grapple transaction inward database – MySQL

Difference betwixt truncate too delete inward SQL

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

10 MySQL ascendance Example

Difference betwixt clustered too nonclustered index inward SQL

Share this post