Copying Data into a New Table : Copy Table « Table Index « SQL / MySQL





Copying Data into a New Table

       
mysql>
mysql> CREATE TABLE CDs
    -> (
    ->     CDID SMALLINT NOT NULL PRIMARY KEY,
    ->     CDName VARCHAR(50) NOT NULL,
    ->     InStock SMALLINT UNSIGNED NOT NULL,
    ->     Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO CDs VALUES
    -> (101, 'Xml', 10, 'Rock'),
    -> (102, 'SQL', 17, 'Jazz'),
    -> (103, 'MySQL', 9, 'Classical'),
    -> (104, 'CSS', 24, NULL),
    -> (105, 'Oracle', 2, 'Blues'),
    -> (106, 'Data type', 12, 'Country'),
    -> (107, 'Flash', 5, 'New Age'),
    -> (108, 'Photoshop', 42, 'Classical'),
    -> (109, 'MacBook', 20, 'Opera'),
    -> (110, 'Notebook', 23, 'Country'),
    -> (111, 'Pascal', 18, 'Jazz'),
    -> (112, 'Ruby', 22, 'Blues'),
    -> (113, 'C', 42, 'Blues');
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE CDs2
    -> (
    -> CDID SMALLINT NOT NULL PRIMARY KEY,
    -> CDName VARCHAR(50) NOT NULL,
    -> InStock SMALLINT UNSIGNED NOT NULL
    -> )
    -> SELECT CDID, CDName, InStock
    -> FROM CDs
    -> WHERE Category='Blues' OR Category='Jazz';
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from CDs;
+------+-----------+---------+-----------+
| CDID | CDName    | InStock | Category  |
+------+-----------+---------+-----------+
|  101 | Xml       |      10 | Rock      |
|  102 | SQL       |      17 | Jazz      |
|  103 | MySQL     |       9 | Classical |
|  104 | CSS       |      24 | NULL      |
|  105 | Oracle    |       2 | Blues     |
|  106 | Data type |      12 | Country   |
|  107 | Flash     |       5 | New Age   |
|  108 | Photoshop |      42 | Classical |
|  109 | MacBook   |      20 | Opera     |
|  110 | Notebook  |      23 | Country   |
|  111 | Pascal    |      18 | Jazz      |
|  112 | Ruby      |      22 | Blues     |
|  113 | C         |      42 | Blues     |
+------+-----------+---------+-----------+
13 rows in set (0.00 sec)

mysql>
mysql> select * from CDs2;
+------+--------+---------+
| CDID | CDName | InStock |
+------+--------+---------+
|  102 | SQL    |      17 |
|  105 | Oracle |       2 |
|  111 | Pascal |      18 |
|  112 | Ruby   |      22 |
|  113 | C      |      42 |
+------+--------+---------+
5 rows in set (0.00 sec)

mysql>
mysql> drop table CDs;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table CDs2;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
    
  










Related examples in the same category

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