MySQL, Oracle, or PostgreSQL whereas modifying desk schema like including new
columns, modifying columns, or dropping columns. Because it’s all the time greatest to have a
backup of a desk that can be utilized in any occasion. I used to be in search of a simple manner
to create an actual copy or duplicate tables which have to be the identical within the schema as properly
as in knowledge, just like creating a replica of the folder. Fortunately there may be a simple SQL
question “CREATE desk table_name AS” which lets you create an actual
copy of the desk by executing only one SQL question. Sure, you learn it
appropriately, no instrument is required to create a backup of the desk you simply must
execute an SQL question.
That is merely superior given its significance and the very best half
of this SQL question is that it really works in nearly all of the databases. I’ve examined it
in MySQL and Oracle however t it ought to work
completely advantageous in different databases like PostgreSQL, SQL Server, and DB2 as properly.
Btw, this SQL question tip is in continuation of my earlier SQL question examples like SQL question to search out duplicate rows in a
desk and SQL question to hitch three tables in MySQL
.
copy desk utilizing SQL question in MySQL? Instance
Now let’s examine it an motion. On this instance I’m creating precise duplicateof desk for demonstration. We are going to use a desk known as AIRCRAFT which has
3 information and after creating backup of AIRCRAFT desk we
will confirm each depend and information to see if its precise duplicate of supply desk
or not. Right here is our SQL question to create
backup of desk in MySQL with none instrument:
create desk table_name as choose * from source_table
supply desk in database. SELECT question instance which is
used to fetch knowledge could be a advanced question which may fetch knowledge from a number of
desk as properly.
— displaying listing of desk earlier than
creating backup
+—————-+
| Tables_in_test |
+—————-+
| plane |
| person |
+—————-+
2 rows IN
SET (0.34 sec)
— creating backup of plane desk
by deciding on all knowledge
aircraft_backup AS SELECT * FROM plane;
Question OK, 3
rows affected (0.14
sec)
Data: 3 Duplicates: 0 Warnings: 0
mysql> SHOW
TABLES;
+—————–+
| Tables_in_test |
+—————–+
| plane |
| aircraft_backup
|
| person
|
+—————–+
3 rows IN
SET (0.00 sec)
— checking variety of information in
supply desk
FROM plane;
+———-+
| depend(*)
|
+———-+
| 3 |
+———-+
1 row IN
SET (0.00 sec)
— verifying variety of information in
newly created backup desk
FROM aircraft_backup;
+———-+
| depend(*)
|
+———-+
| 3 |
+———-+
1 row IN
SET (0.00 sec)
— knowledge in authentic desk
+—–+——–+—————+
| help |
aname | cruisingrange |
+—–+——–+—————+
| 301 | Boeing |
16000 |
| 302 | Airbus |
10000 |
| 303 | Jet |
8000 |
+—–+——–+—————+
3 rows IN
SET (0.00 sec)
— knowledge in backup desk ought to be
precisely similar with supply desk
mysql> SELECT * FROM aircraft_backup;
+—–+——–+—————+
| help |
aname | cruisingrange |
+—–+——–+—————+
| 301 | Boeing |
16000 |
| 302 | Airbus |
10000 |
| 303 | Jet |
8000 |
+—–+——–+—————+
3 rows IN
SET (0.00 sec)
create a desk from one other desk in SQL
creating desk from one other desk in SQL is similar as copying desk however you could have a selection
to both simply copy the schema or copy schema and knowledge collectively. In an effort to
create SQL desk from one other desk simply use following create desk SQL question
and change title of desk with precise title you need.
create desk destination_table as choose * from
source_table;
In an effort to create desk by copying schema from one other desk with out
knowledge use a situation in WHERE clause which all the time returns false.
AIRCRAFT_SCHEMA_BACKUP AS SELECT * FROM AIRCRAFT WHERE
3=4;
Question OK, 0
rows affected (0.11
sec)
Data: 0 Duplicates: 0 Warnings: 0
mysql> SELECT
* FROM
AIRCRAFT_SCHEMA_BACKUP;
Empty SET (0.00 sec)
mysql> DESCRIBE
AIRCRAFT_SCHEMA_BACKUP;
+—————+————-+——+—–+———+——-+
| FIELD
| Sort
| NULL | KEY | DEFAULT |
Additional |
+—————+————-+——+—–+———+——-+
| help | int(11)
| NO |
| NULL
| |
| aname | varchar(20) | YES |
| NULL
| |
| cruisingrange |
int(11) |
YES | | NULL
| |
+—————+————-+——+—–+———+——-+
3 rows IN
SET (0.06 sec)
f you wish to create a desk from one other desk with knowledge and schema than
simply execute above SQL question with out WHERE clause.
In case if you don’t need your new desk to accommodates all columns and solely
few columns from the unique desk than as a substitute of utilizing choose
* simply use choose column, column and so on as proven in under SQL question:
AIRCRAFT_BK AS SELECT help,
aname FROM AIRCRAFT;
Question OK, 3
rows affected (0.13
sec)
Data: 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 is all on making a backup of desk or copying desk through the use of SQL
question. We have now seen tips on how to copy tables, tips on how to create desk from one other desk
with knowledge and with out knowledge and tips on how to create duplicates of desk. You all the time
have flexibility on selecting columns or knowledge.
Different database and SQL tutorials from Javarevisited
