Copy Table Demo : Copy Table « Table Index « SQL / MySQL






Copy Table Demo

  
/*

mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.01 sec)

mysql> Select * from Employee;
+----------+---------------+------+
| Name     | PhoneNo       | Age  |
+----------+---------------+------+
| John Doe | Unknown Phone |   31 |
+----------+---------------+------+
1 row in set (0.00 sec)

mysql> /* Now copy the table */
mysql> CREATE TABLE MyEmployee SELECT * FROM Employee ;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from MyEmployee;
+----------+---------------+------+
| Name     | PhoneNo       | Age  |
+----------+---------------+------+
| John Doe | Unknown Phone |   31 |
+----------+---------------+------+
1 row in set (0.01 sec)

*/
Drop TABLE Employee;
Drop TABLE MyEmployee;

CREATE TABLE Employee (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
   Phone VARCHAR(15) DEFAULT 'Unknown Phone',
   Age     INT CHECK (Age BETWEEN 20 and 30));

Describe Employee;

INSERT INTO Employee (Name, Phone, Age) VALUES ('Joe Wang', '666 2323', 26);
INSERT INTO Employee (Name, Age) VALUES ('John Doe', 31);
INSERT INTO Employee (Name, Phone) VALUES ('Joe Wang', NULL);

Select * from Employee;


/* Now copy the table */  
CREATE TABLE MyEmployee SELECT * FROM Employee ;

select * from MyEmployee;

           
         
    
  








Related examples in the same category

1.Copy Table with Condition
2.Copying a Table
3.Use NULL in where clause
4.Copy table with conditions
5.Only copy records
6.Using the INSERT Statement to Copy Data
7.Using the REPLACE Statement to Copy Data
8.Copy a table
9.Only copy certain columns
10.Copy one row of data
11.Copy data to temporary table
12.Creating New Tables with SELECT Results
13.Copying Only Selected Data from a Table
14.Copy table with calculation
15.Copying Data into a New Table
16.MySQL truncates the data during copying