MySQL INTO OUTFILE Query problems

Question

I have written this simple query which would pull out all the data from table into a CSV file.

SELECT Group_concat(Concat(column_name)) 
FROM   information_schema.columns 
WHERE  table_name = 'subject_assignment' 
       AND table_schema = 'newschema2' 
UNION ALL 
SELECT (SELECT Group_concat('`', column_name, '`') 
        FROM   information_schema.columns 
        WHERE  table_name = 'subject_assignment' 
               AND table_schema = 'newschema2') 
FROM   subject_assignment 
INTO OUTFILE 'D:\\export\\asd.csv'

Now, the first bit works great but I have issues with the second part. Instead of pulling out data from columns specified in column list it just displays me all the column names over and over again.

Could you please suggest what I am doing wrong?

Thanks.

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

In your second SELECT you do not select any column from subject_assignment. Instead, you're selecting single string value made from concatenated column names. And you're selecting it as many times as the row count of subject_assignment.

UPDATE:

If you want to dynamically create column names and then select data from them, see this: https://stackoverflow.com/a/17573774/925196

edited May 23 '17 at 12:27

Community

1
1

answered May 15 '14 at 13:20

Grzegorz Adam Kowalski

5,243
3
29
40

How can I dynamically specify column names then? There are around 80 tables that I need to extract information from into a CSV files and I just want to find a way to dynamically specify column names in the second select. – Arturs Kirsis May 15 '14 at 13:23

MySQL INTO OUTFILE Query problems

1 Answers1