| | |
SQL COPY TABLE Command
For backup and other requirements we often have to copy the data of a table or copy the total table structure with data. We can selectively copy the data of a MySQL table to a new table or copy the total data to a new table. We will learn here different techniques on how to do this.
We can copy a table structure and records to another new table. The CREATE
TABLE command will create a table with same structure of the old table and add
all the records. To export data to an existing table you can use insert command.
CREATE TABLE student2 SELECT * FROM student
This will create a new table class2 using the structure of the table class
and will copy all the records from table class to our new table class2.
This became more useful when we add conditions to this by using SQL WHERE
command. This way selectively we can transfer records to a new table. This is
our table.
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 2 |
Max Ruin |
Three |
85 |
| 3 |
Arnold |
Three |
55 |
| 4 |
Krish Star |
Four |
60 |
| 5 |
John Mike |
Four |
60 |
| 6 |
Alex John |
Four |
55 |
We will apply our sql command to this table to create a new table and we will
copy records for which class = Four. So our new table will contain the
records of class four only.
CREATE TABLE student2 SELECT * FROM student WHERE class='Four'
With this command we will create a new table student2 of same structure of
main table student and all the records of class = Four will be copied to the new
table. The new table student2 will have these records
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 4 |
Krish Star |
Four |
60 |
| 5 |
John Mike |
Four |
60 |
| 6 |
Alex John |
Four |
55 |
This way we can use any conditional requirements by using where clause to create
or copy different tables.
Copy table structure only
WE can copy only structure and create a new table like this.
create table t1 like student
Here we will create a new table t1 by using structure of student table. ( No data is copied)
Create table if not exists
Note that all the above quires will return error if the table is already exist, so to prevent this error message we can add the command IF NOT EXISTS to the query.
CREATE TABLE IF NOT EXISTS student5 SELECT * FROM student WHERE class='Four'
Here the table will be created only if the table is not there before.
What we will do if we want to delete the old table and create a new table ?
DROP TABLE IF EXISTS
Some time we may not be sure if the table exists or not so we can drop the table if exist by adding one more query before creating the table. Here it is
DROP TABLE IF EXISTS `student5`;
The advantage of the above command over using a simple drop table command is here no error message saying unknown table is generated even if the table is not there.
| |
| | dfgf | 19-02-2009 |
|---|
This is a very useful information.
Thank you very much....
Everywhere else i found info to do this in 2 steps, but this method saves a lot of work. | | demonsmile | 15-03-2009 |
|---|
| wa bng clear discussion about sa copying of data in the table to table | | Hiromitsu | 05-06-2009 |
|---|
| Thanks for this tutorial. It works great !! | | siddhartha singh | 10-06-2009 |
|---|
| this is very clear cut way to explain the things,one can easily learn the points by just reading the txts | | Rudy Warjri | 09-07-2009 |
|---|
| please giv me a more detailed explanation to calculate the marks from one table and then calculate the total sum in another table by using some php code | | Willy | 06-08-2009 |
|---|
| Will this work in ACCESS 2007? | | matthew | 08-10-2009 |
|---|
| It doesn't work very well. Particular fields of newly created table has to be altered if there were extra properties in the fields of the source. | | Fadi | 10-12-2009 |
|---|
| Greate work thanks. It really helped me | | Abid | 18-12-2009 |
|---|
| Thanks for this info.. but if what if i've just to copy only the structure.....?? | | Dave | 22-02-2010 |
|---|
always double-check that the new table has the same indexes as the source table - some versions don't copy the indexes when you do a
CREATE TABLE student2 SELECT * FROM student
with or without the LIMIT 0
:) | | seenu | 02-03-2010 |
|---|
| Thanks for providing the valuable information, this helps a lot to learn the concept's. | | Vilart | 08-03-2010 |
|---|
| Thank so much for your best information. God bless you. | | Anand | 09-03-2010 |
|---|
Hi, this query will work...
select * into A from B
where A- new table name and B - old table name.. the table with the same column, data and etc,etc are created... this worked in SQL 2005 |
|
|
|
|
|
|
If you are facing any problem in viewing this page, please tell us
