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

Many times nosotros withdraw to practice 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 e'er best to conduct maintain a backup of tabular array which tin survive used inward whatever event. I was looking for an slow agency to create exact re-create or duplicate tables which must survive same inward schema every bit good every bit inward 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 practice exact re-create of tabular array past times executing just 1 SQL query. Yes, you lot read it correctly, no tool is required to practice backup of tabular array you lot just withdraw to execute an SQL query. This is just awesome given its importance as well as best operate of this SQL inquiry is that it industrial plant inward nigh all the database. I conduct maintain tested it inward MySQL as well as Oracle but t it should piece of job perfectly discovery inward other databases similar PostgreSQL, SQL Server as well as DB2 every bit well. This SQL inquiry tip is inward continuation of my before SQL inquiry examples similar SQL inquiry to discovery duplicate rows inward a table as well as SQL inquiry to bring together 3 tables inward MySQL .

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

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

create tabular array table_name every bit conduct * from source_table

where table_name is advert of backup tabular array as well as source_table is advert of source tabular array inward database. SELECT inquiry example which is used to fetch information tin survive a complex inquiry 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)

-- 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)

-- checking set out of records inward source tabular array

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

-- verifying set out 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;
+-----+--------+---------------+
| help | aname  | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing |         16000 |
| 302 | Airbus |         10000 |
| 303 | Jet    |          8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)

-- information inward backup tabular array should survive just 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)

How to practice tabular array from unopen to other tabular array inward SQL

creating tabular array from unopen to other tabular array inward SQL  is same every bit copying tabular array but you lot conduct maintain a selection to either just re-create the schema or re-create schema as well as information together. In social club to practice SQL tabular array from unopen to other tabular array just utilization next practice tabular array SQL inquiry as well as supersede advert of tabular array amongst exact advert you lot want.

create tabular array destination_table every bit conduct * from source_table;

In social club to practice tabular array past times copying schema from unopen to other tabular array amongst out information utilization a status inward WHERE clause which e'er 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)

f you lot desire to creat a tabular array from unopen to other tabular array amongst information as well as schema than just execute to 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 exclusively few columns from the master tabular array than instead of using select * just utilization conduct column, column etc every bit 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;
+-----+--------+
| 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 conduct maintain seen how to re-create tables, how to practice tabular array from unopen to other tabular array amongst information as well as without information as well as how to practice duplicates of table. You e'er conduct maintain flexibility on choosing columns or data.

Further Learning
How to cope transaction inward database – MySQL

Difference betwixt truncate as well as delete inward SQL

How to conduct maintain backup of database using Mysqldump utility inward MySQL

10 MySQL ascendency Example

Difference betwixt clustered as well as nonclustered index inward SQL

Sumber https://javarevisited.blogspot.com/

Share this post