SELECT … UNION … INTO OUTFILE

5Nov/100

SELECT … UNION … INTO OUTFILE

Quick Tip:

Sometimes you need to dump some data directly to a file.
That's easy by issuing a SELECT ... INTO OUTFILE .... FROM ...

But what happens when you need to retrieve data from in some particular way or when you want to add "headers" to the columns you're outputting?
Likely you'll get an error that UNION statement is not allowed to play with INTO OUTFILE.

So this is the way to output data with UNION:

PLAIN TEXT

SQL:

SELECT *
INTO OUTFILE '/dir/to/file.csv'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM (
( SELECT 'FieldA','FieldB','FieldC','FieldD' )
UNION
( SELECT FieldA, FieldB, FieldC, FieldD FROM my_table)
) AS T

In that way you're making a compound table from the UNION statement. Then, you select * from that table to the outfile.
Remember to ALWAYS give an alias to a derived table :)

Happy Dumping!