SELECT INTO OUTFILE

There are times when, for example I am about to run a large database update or compare two tables, I need to output a simple copy of a SELECT statement to a file for reference at a later date.

After creating a script to handle the task, I realized that MySQL will do all this work for me. All I need to do is create the SELECT query, and append the INTO OUTFILE syntax to generate my file.

Below is a simple example of SELECT … INTO OUTFILE:

SELECT   *
FROM     Customers
INTO OUTFILE 'C:/MySQL_Output/Customers_Output.txt'
FIELDS  TERMINATED BY '|'
        ENCLOSED BY '"'
LINES TERMINATED BY '\n';

We are asking MySQL to SELECT all records from the Customers table and output the query results to the MySQL_Output folder in a file called Customers_Output.txt with some specific formatting. In the example above I am asking MySQL to terminate all fields from the result set with a | and enclose each field with a ” and at the end of a result set, output a new line.

The output from such a request would look like the following:

"ABC Widgets"|"12345 Simple Street"|"Richmond"|"British Columbia"
"XYZ Fasteners"|"78910 Enterprise Avenue"|"Vancouver"|"British Columbia"