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

Many times nosotros require to exercise 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 lead maintain a backup of tabular array which tin last used inwards whatever event. I was looking for an slow means to create exact re-create or duplicate tables which must last same inwards schema equally good equally inwards data, similar to creating re-create of folder. Luckily at that topographic point is an slow SQL question "CREATE tabular array table_name AS" which allows you lot to exercise exact re-create of tabular array past times executing just ane SQL query. Yes, you lot read it correctly, no tool is required to exercise backup of tabular array you lot just require to execute an SQL query. This is only awesome given its importance in addition to best run of this SQL question is that it plant inwards most all the database. I lead maintain tested it inwards MySQL in addition to Oracle but t it should piece of employment perfectly detect inwards other databases similar PostgreSQL, SQL Server in addition to DB2 equally well. This SQL question tip is inwards continuation of my before SQL question examples similar SQL question to detect duplicate rows inwards a table in addition to SQL question to bring together 3 tables inwards MySQL .

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

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

create tabular array table_name equally choose * 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 question example which is used to fetch information tin last a complex question 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 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 release of records inwards source tabular array

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

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

-- information inwards backup tabular array should last just same amongst source table

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

How to exercise tabular array from roughly other tabular array inwards SQL

creating tabular array from roughly other tabular array inwards SQL  is same equally copying tabular array but you lot lead maintain a alternative to either just re-create the schema or re-create schema in addition to information together. In lodge to exercise SQL tabular array from roughly other tabular array just utilisation next exercise tabular array SQL question in addition to supercede elevate of tabular array amongst exact elevate you lot want.

create tabular array destination_table equally choose * from source_table;

In lodge to exercise tabular array past times copying schema from roughly other tabular array amongst out information utilisation 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 |
+---------------+-------------+------+-----+---------+-------+
| aid           | 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 roughly other tabular array amongst information in addition to schema than just execute to a higher house SQL question without WHERE clause.

In instance if you lot don't desire your novel tabular array to contains all columns in addition to solely few columns from the master copy tabular array than instead of using select * just utilisation choose 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;
+-----+--------+
| aid | 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 lead maintain seen how to re-create tables, how to exercise tabular array from roughly other tabular array amongst information in addition to without information in addition to how to exercise duplicates of table. You ever lead maintain flexibility on choosing columns or data.

Further Learning
How to contend transaction inwards database – MySQL

Difference betwixt truncate in addition to delete inwards SQL

How to accept backup of database using Mysqldump utility inwards MySQL

10 MySQL ascendance Example

Difference betwixt clustered in addition to nonclustered index inwards SQL

Bagikan Artikel ini