SQL COPY TABLE Command ( CREATE TABLE ... )

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.

Read how INSERT ... SELECT with ON DUPLICATE KEY UPDATE is used to export data to an existing table

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 student2 using the structure of the table student and will copy all the records from table student to our new table student2.

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.

Copy with extra features like auto_increment

To copy the extra features of the column like auto_increment , unique etc we have to use like this

CREATE TABLE student2 (id INT(3) auto_increment primary key) SELECT student.name,student.class, student.mark from student

Using SHOW CREATE TABLE

We can use query to generate sql dump for creation of a table. The output of above query can be used to create another table. Here is the query.

SHOW CREATE TABLE student

Script using PHP PDO

Here is a script using php pdo to generate CREATE TABLE query. We assumed that you already have database connection string inside config.php file.

<?Php
require "config.php"; // Database connection string
$table_name='student';
$q = $dbo->prepare("SHOW CREATE TABLE $table_name ");
$q->execute();
$table = $q->fetchAll();

print_r($table);
echo '<br><br>';
echo $table[0]['Create Table'];
?>

The output is here

Array ( [0] => Array ( [Table] => student [0] => student [Create Table] => CREATE TABLE `student` ( `id` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', `class` varchar(10) NOT NULL DEFAULT '', `mark` int(3) NOT NULL DEFAULT '0', `sex` varchar(6) NOT NULL DEFAULT 'male', UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 [1] => CREATE TABLE `student` ( `id` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', `class` varchar(10) NOT NULL DEFAULT '', `mark` int(3) NOT NULL DEFAULT '0', `sex` varchar(6) NOT NULL DEFAULT 'male', UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 ) )

CREATE TABLE `student` ( `id` int(2) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL DEFAULT '', `class` varchar(10) NOT NULL DEFAULT '', `mark` int(3) NOT NULL DEFAULT '0', `sex` varchar(6) NOT NULL DEFAULT 'male', UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8

Download sql dump of this student table

Number of User Comments : 26


Google+

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
manoj kumar bardhan

07-04-2010

Its very help full..
JAISHI RAM

22-05-2010

I want to copy table1 into table2 with structure and data in the same database. I used the cammand CREATE TABLE student2 SELECT * FROM student on button click event and also on sql moblie query but can not make copy. So u r requested to pl. kindly solve my this problem with example. Thanking u.
Raaj

04-06-2010

i want to copy table structure only in SQL2005.. can you please help me?
Scot King

10-06-2010

How do I copy data from table into tablebackup that is external to the current database?
Satish

05-08-2010

Thanks for the info, anyone have tired to create a Multiple Tables with the Automatic Names given to New Table Created , Queried from a Master Table in same database Example: "mstr_Student_tbl". Here I want a Table generated as "tbl_stundentID" automatically where the structure is same as in a Model Table Model_Stundent_tbl If that is a SQL It will be like ..?? just 2 bit to start... CREATE TABLE mstr_Student_tbl.ID LIKE Model_Student_tbl Thanks in advance for help regards Satish
Pankaj Kumar GUpta

29-09-2010

i try to copy only structure and create a new table and use this Query "create table t1 like student" when i use this Query it not work any one give me suggestion
sam

08-11-2010

how to merge two tables in php mysql database? Same field name records not deleted.All records save in new table.
Adil

13-01-2011

@Raaj -- copy table structure only no data; CREATE TABLE Table_NAME SELECT * FROM Table_NAME_copy where 1 = 2;
el-ahmed mahmood

09-02-2011

i would like a SQL statement that define the structure and content of a table containing student profile
Narendra Kumar

16-06-2011

I would like to told you that how to copy the one table data into another. INSERT INTO Table1 (Column1, ..., ColumnN) SELECT Column1, ..., ColumnN FROM Table2
sunny

09-09-2011

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. For example: INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
ashish shukla

01-11-2012

its very benificial for fresher,.....
Saeed

07-11-2014

Thank you so much .... This website very helpfull ...*****

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

What is the most important factor of a web site

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer