MYSQL COPY TABLE Command

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  New_users  SELECT * FROM users

Copy table structure only

WE can copy only structure and create a new table like this.

create table New_users like users

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  New_users  SELECT * FROM users WHERE UserType=’Admin’

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 ` New_users `;

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  New_users  (recID INT(3) auto_increment primary key) SELECT * student.name,student. UserType, student.Password FROM users