Load data and set division : LOAD « Command MySQL « SQL / MySQL






  1. SQL / MySQL
  2. Command MySQL
  3. LOAD

Load data and set division

      
mysql>
mysql>
mysql>
mysql> CREATE   TABLE TEAMS
    ->         (TEAMNO         INTEGER      NOT NULL,
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          DIVISION       CHAR(6)      NOT NULL,
    ->          PRIMARY KEY    (TEAMNO)             );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO TEAMS VALUES (1,  6, 'first');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO TEAMS VALUES (2, 27, 'second');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> LOAD DATA INFILE 'C:/TEAMS.TXT'
    -> REPLACE
    -> INTO TABLE TEAMS
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '?'
    -> SET DIVISION='xxx'
    -> ;
Query OK, 2 rows affected, 3 warnings (0.00 sec)
Records: 1  Deleted: 1  Skipped: 0  Warnings: 3

mysql> SELECT * FROM TEAMS;
+--------+------------+----------+
| TEAMNO | EmployeeNO | DIVISION |
+--------+------------+----------+
|      1 |          0 | xxx      |
|      2 |         27 | second   |
+--------+------------+----------+
2 rows in set (0.00 sec)

mysql>
mysql> drop table teams;
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 from a text file to table
9.Load data with FIELDS clause
10.Inserting High Volumes of Data from a Text File with LOAD DATA
11.Load data and replace
12.Load data, replace and ignore
13.Load data to specific columns
14.Importing Data into a Table