Load data from a text file to 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> SELECT CDName, InStock, Category INTO OUTFILE 'CDsCountry.sql'
    -> FIELDS
    -> TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> FROM CDs WHERE Category='Country';
mysql>
mysql> CREATE TABLE CDs3
    -> (
    ->     CDName VARCHAR(50) NOT NULL,
    ->     InStock SMALLINT UNSIGNED NOT NULL,
    ->     Category VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> LOAD DATA INFILE 'CDsCountry.sql'
    -> INTO TABLE CDs3
    -> FIELDS
    -> TERMINATED BY ','
    -> ENCLOSED BY '"';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql> select * from CDs3;
+-----------+---------+----------+
| CDName    | InStock | Category |
+-----------+---------+----------+
| Data type |      12 | Country  |
| Notebook  |      23 | Country  |
+-----------+---------+----------+
2 rows in set (0.00 sec)

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

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

mysql>

Related examples in the same category

1.	Database directories are located directly under the data directory
2.	The syntax of LOAD DATA is as follows:
3.	Use LOAD DATA to load a file into a database other than the current one, you can qualify the table name with the database name.
4.	LOAD DATA assumes the columns in the datafile have the same order as the columns in the table.
5.	Load a datafile containing values quoted by double quote characters
6.	Load a file that has data values separated by commas, and lines ending with carriage returns
7.	The LOAD DATA INFILE Statement
8.	Load data with FIELDS clause
9.	Inserting High Volumes of Data from a Text File with LOAD DATA
10.	Load data and replace
11.	Load data, replace and ignore
12.	Load data to specific columns
13.	Load data and set division
14.	Importing Data into a Table

Load data from a text file to table : LOAD « Command MySQL « SQL / MySQL

Related examples in the same category