Search code examples
mysqlsqlinto-outfile

How to get all rows from table using MySQL OUTFILE

I'm using MySQL to output the contents of a database table to a CSV file using the following syntax:

SELECT 'Keyword_1_Frequency', 'Keyword_2_Frequency', 'Keyword_3_Frequency', 
'Keyword_4_Frequency', 'Keyword_5_Frequency', 'Keyword_6_Frequency', 
'Keyword_7_Frequency', 'Keyword_8_Frequency', 'Keyword_9_Frequency', 
'Keyword_10_Frequency', 'Keyword_11_Frequency', 'Keyword_12_Frequency','RSSFeedCategoryDescription'  
UNION (SELECT Keyword_1_Frequency, Keyword_2_Frequency, Keyword_3_Frequency, 
Keyword_4_Frequency, Keyword_5_Frequency, Keyword_6_Frequency, Keyword_7_Frequency, 
Keyword_8_Frequency, Keyword_9_Frequency, Keyword_10_Frequency, Keyword_11_Frequency, 
Keyword_12_Frequency, RSSFeedCategoryDescription   
FROM dm_16_945848_18042011_1303128210855  
INTO OUTFILE 'C:/temp/dm_16_945848_18042011_1303128210855.csv'  
FIELDS TERMINATED BY ','  
LINES TERMINATED BY '\n');

The first SELECT clause is simply to get the headers of the columns as the first row of the CSV file.

This query seems to be working but for some maddening reason, it is not extracting all of the rows from the table. For eg, if I have 12 rows in the table, it gets 10.

Can anyone explain why? I can see nothing on the MySQL support pages for this.

Thanks

Mr Morgan.

Solution

  • Probably you have duplicates in your table and UNION eliminates them.

    Try replacing UNION with UNION ALL.