Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Store The Results Of A Query In A File With MySQL
From Wiki
You are running MySQL and would like to store the output of a query in a file. How can you do this? You can use SELECT INTO OUTFILE to do this.
Here is how, first create this table- CREATE TABLE TestOutput(Col1 INT, Col2 VARCHAR(30),Col3 DATETIME);
Then insert these 3 rows
- INSERT TestOutput VALUES (1,'test1','20080903');
- INSERT TestOutput VALUES (2,'test2','20080904');
- INSERT TestOutput VALUES (3,'test3','20080905');
On a non Windows box you would run something like this:
- SELECT Col1,Col2,Col3
- FROM TestOutput
- INTO OUTFILE '/tmp/result.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY '\n'
On a Windows box, run this:
- SELECT Col1,Col2,Col3
- FROM TestOutput
- INTO OUTFILE 'result.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY '\n'
Run that code we just had above again. You see what happened? You got error 1086: File already exists. Another problem is that you don't know where this file will be created. Is should be in MySQL\MySQL Server 5.0\data\<your default schema>
Of course you can also specify the exact location, just make sure to escape the slashes
- SELECT Col1,Col2,Col3
- FROM TestOutput
- INTO OUTFILE 'c:\\result.txt'
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- LINES TERMINATED BY '\n'
To find more info about this check out the MySQL documentations 12.2.7. SELECT Syntax
Contributed By --SQLDenis 15:39, 3 September 2008 (GMT)
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.