SQL PHP HTML ASP JavaScript articles and free scripts to download
 

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 here 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'
Related Tutorial
Exporting data to table
SQL Inner Join
Number of Affected rows
SQL Rename table
PHP MySQL functions

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.

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.
Discuss this tutorial at forum

List of SQL Tutorials


Further readings
Creating a new table by using data from one table
Copying data from one table to another table
Updating another table with data from main table
Update SQL commands
Inserting SUM, AVG data from one table column to other using group by command
Create table query with if exists sql with php script
 
Scripts
PHP
JavaScript
HOME
SQL Tutorial List
SQL (Home)
SQL Commands
AVG
Alter Table
Between
Count
Copy Table
Create Table
Delete
Distinct
Group by
Having
Insert
Inner Join
IN
Left join
Limit
Like
MAX
MIN
Order By
OR AND
Rand
Replace
Rename Table
Select Query
Sum
Union
Update
Where
Subscribe
Submit your email address and receive article and product notifications. Your email is safe with us.