Thursday, December 20, 2012

SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database

Many times we need to create backup or copy of tables in database like MySQL, Oracle or PostgreSQL while modifying table schema like adding new columns, modifying column or dropping columns. Since its always best to have a backup of table which can be used in any event. I was looking for an easy way to create exact copy or duplicate tables which must be same in schema as well as in data, similar to creating copy of folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create exact copy of table by executing just one SQL query. Yes, you read it correctly, no tool is required to create backup of table you just need to execute an SQL query. This is simply awesome given its importance and best part of this SQL query is that it works in almost all the database. I have tested it in MySQL and Oracle but t it should work perfectly find in other databases like PostgreSQL, SQL Server and DB2 as well. This SQL query tip is in continuation of my earlier SQL query examples like SQL query to find duplicate rows in a table and SQL query to join three tables in MySQL .

How to copy table using SQL query in MySQL

How to copy or backup table in SQL query exampleNow let's see it an action. In this example I am creating exact replica of table for demonstration. We will use a table called AIRCRAFT which has 3 records and after creating backup of AIRCRAFT table we will verify both count and records to see if its exact replica of source table or not. Here is our  SQL query to create backup of table in MySQL without any tool:

create table table_name as select * from source_table

where table_name is name of backup table and source_table is name of source table in database. SELECT query example which is used to fetch data can be a complex query which can fetch data from multiple table as well.


-- showing list of table before creating backup
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| aircraft       |
| user           |
+----------------+
2 rows IN SET (0.34 sec)


-- creating backup of aircraft table by 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 number of records in source table
mysql> SELECT count(*) FROM aircraft;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row IN SET (0.00 sec)


-- verifying number of records in newly created backup table
mysql> SELECT count(*) FROM aircraft_backup;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row IN SET (0.00 sec)


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

-- data in backup table should be exactly same with 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 create table from another table in SQL

creating table from another table in SQL  is same as copying table but you have a choice to either just copy the schema or copy schema and data together. In order to create SQL table from another table just use following create table SQL query and replace name of table with exact name you want.

create table destination_table as select * from source_table;

In order to create table by copying schema from another table with out data use a condition in WHERE clause which always 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 want to creat a table from another table with data and schema than just execute above SQL query without WHERE clause.

In case if you don't want your new table to contains all columns and only few columns from the original table than instead of using select * just use select column, column etc as shown in 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 table or copying table by using SQL query. We have seen how to copy tables, how to create table from another table with data and without data and how to create duplicates of table. You always have flexibility on choosing columns or data.

Other database and SQL tutorials from Javarevisited

No comments :

Post a Comment