3

I'm revising my original question significantly...

This works perfectly for dumping your table to a CSV with the field names on the first row.

SELECT 'field1', 'field2', 'field3' 
UNION SELECT * INTO OUTFILE '/var/tmp/table_name.csv' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' FROM table_name;

However, when you have hundreds of fields and new fields are added or fields are changed or deleted from time to time I need something dynamic that will put the field names in the format above dynamically so this query will work in a script anytime without having to manually update the field names by hand.

user1295563
  • 39
  • 1
  • 1
  • 3

1 Answers1

11

How about -

SELECT CONCAT(GROUP_CONCAT(COLUMN_NAME SEPARATOR ','), "\n")
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'tbl'
GROUP BY TABLE_NAME;

You could pass the output from this to a new_file and then append the data from the the outfile query -

cat /var/tmp/table_name.csv >> new_file
user1191247
  • 10,808
  • 2
  • 22
  • 32
  • Thanks for the answer, I have revised my question to be more specific though. – user1295563 Mar 27 '12 at 14:17
  • I can't think of a way to do it with UNION but you can use the above to add the file created by your `SELECT * INTO OUTFILE...` to the ouput from the information schema query. – user1191247 Mar 27 '12 at 14:35
  • 6
    Around here we say thanks by accepting answers. Just click on the tick to the left of the answer you want to accept. – user1191247 Mar 27 '12 at 16:45