Mysql Loader Control File
Mysql Loader Control File is the mean to import a data to a plain text from
Mysql database.
Understand with Example
The Tutorial illustrate an example from 'Mysql Loader Control File'.To
grasp this example we create a table 'employee1' with required fieldnames and
datatypes respectively.
Query to Create Table named employee1:
mysql>CREATE TABLE employee1 (
-> Empid int(10),
-> Empname varchar(60),
-> date date
-> );
|
Query to insert data into Table named employee1:
The insert into is used to add the records or rows into a table
'employee1'.
mysql>insert into employee1 values(01,'Girish','2008-12-20');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-21');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(03,'vineet','2008-12-21');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(04,'Amit','2008-12-20');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-23');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Sandeep','2008-12-24');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'suman','2008-12-25');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(01,'AAA',' 2008-12-03');
Query OK, 1 row affected (0.02 sec)
|
Query to view data of Table named employee1:
The select query is used to view the detail of table 'employee1'.
mysql> select * from employee1;
+-------+---------+------------+
| Empid | Name | DOB |
+-------+---------+------------+
| 1 | Girish | 2008-12-20 |
| 2 | Komal | 2008-12-21 |
| 3 | vineet | 2008-12-21 |
| 4 | Amit | 2008-12-20 |
| 2 | Komal | 2008-12-23 |
| 2 | Sandeep | 2008-12-24 |
| 2 | suman | 2008-12-25 |
| 1 | AAA | 2008-12-03 |
+-------+---------+------------+
8 rows in set (0.00 sec)
|
Query to create loader control file of Table named employee1:
The Query below is used to import the table 'employee1' from Mysql database
to a plain text 'employee.txt'.
mysql> select * into outfile 'C:/Documents and Settings/Administrator/Desktop/employee.txt'
-> fields terminated by '\t'
-> lines terminated by '\n'
-> from employee1;
Query OK, 8 rows affected (0.01 sec)
|
Output :-
1 Girish 2008-12-20
2 Komal 2008-12-21
3 vineet 2008-12-21
4 Amit 2008-12-20
2 Komal 2008-12-23
2 Sandeep 2008-12-24
2 suman 2008-12-25
1 AAA 2008-12-03
|
Query to create table in which the data of the above file(employee.txt) is
loaded:
The Query is used to create a table 'Load Employee' in which the data from
employee.txt is loaded into a table 'LoadedEmployee'.The format of the created
table and the text plain should be same.
mysql> CREATE TABLE LoadedEmployee (
-> Empid int(10),
-> Empname varchar(60),
-> date date
-> );
Query OK, 0 rows affected (0.03 sec)
|
Query to load data from control file created above of Table named
employee1:
The below Query helps you to load the data file 'employee.txt' into
table 'Loaded Employee'. The SQL has a field delimiter. The delimiter tab
was replaced by \t.
mysql> LOAD DATA INFILE 'C:/Documents and Settings/Administrator/Desktop/employee.txt'
-> INTO TABLE LoadedEmployee
-> FIELDS TERMINATED BY '\t'
-> LINES TERMINATED BY '\n';
Query OK, 8 rows affected (0.05 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
|
Query to show data loaded in table loadedEmployee:
mysql> select * from loadedEmployee;
+-------+---------+------------+
| Empid | Empname | date |
+-------+---------+------------+
| 1 | Girish | 2008-12-20 |
| 2 | Komal | 2008-12-21 |
| 3 | vineet | 2008-12-21 |
| 4 | Amit | 2008-12-20 |
| 2 | Komal | 2008-12-23 |
| 2 | Sandeep | 2008-12-24 |
| 2 | suman | 2008-12-25 |
| 1 | AAA | 2008-12-03 |
+-------+---------+------------+
8 rows in set (0.00 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
