MySQL LOAD Command

Database directories are located directly under the data directory : MySQL  LOAD  Command

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)

Load data with FIELDS clause

      
mysql>
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> SELECT CDName, InStock, Category INTO OUTFILE 'CDs3.sql'
    -> FROM CDs WHERE Category='New Age';
mysql>
mysql>
mysql> SELECT CDName, InStock, Category INTO OUTFILE 'CDs3.sql'
    -> FIELDS
    -> TERMINATED BY ','
    -> ENCLOSED BY '*'
    -> FROM CDs WHERE Category='Classical';
mysql>
mysql>
mysql> drop table CDs;
Query OK, 0 rows affected (0.00 sec)

Database directories are located directly under the data directory

      

LOAD DATA INFILE 'mytbl.txt' INTO TABLE mytbl;
LOAD DATA INFILE 'yourFolder/mytbl.txt' INTO TABLE mytbl;

The syntax of LOAD DATA is as follows:

      

LOAD DATA [loadoptions] INFILE 'filename.txt' [duplicateoptions]
INTO TABLE tablename [importoptions] [IGNORE ignorenr LINES]
[(columnlist)]

LOAD DATA INFILE 'c:/import1-latin1.txt'
INTO TABLE importtable1
FIELDS TERMINATED BY '\t'
ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n'

LOAD DATA INFILE '/tmp/import2.txt' REPLACE
INTO TABLE importtable2
FIELDS ENCLOSED BY '\"'

The LOAD DATA INFILE Statement

      


LOAD DATA INFILE 'file_name' INTO TABLE table_name;

LOAD DATA INFILE '/var/mysql/data/test/data.txt' INTO TABLE t;

LOAD DATA assumes the columns in the datafile have the same order as the columns in the table.

      

mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl (b, c, a);

Load a file that has data values separated by commas, and lines ending with carriage returns

      

shell> mysqlimport --fields-terminated-by=,--lines-terminated-by="\r" world City.txt

Load a datafile containing values quoted by double quote characters

      
shell> mysqlimport --fields-terminated-by='"' world City.txt

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.

      

mysql> LOAD DATA LOCAL 'mytbl.txt' INTO TABLE other_db.mytbl;