SQL Create Queries And Redirect To File

Question

I've written a query and have filtered out the E.G "Salaries over $120,000."

I want to redirect the answer to a .csv file, however Im experiencing issues.

Ive read online that : To redirect the output of your queries to a file, append the following

clause to your queries:

INTO OUTFILE <filename> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

However: when I append this to my query...

SELECT from_date FROM dept_emp_latest_date WHERE from_date < '2002-08-01' and from_date > '2002-07-01' INTO OUTFILE new_hires.csv FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

I just get a syntax error.

The query below works fine. But I need help to redirect it to my file... new_hires.csv.

SELECT from_date FROM dept_emp_latest_date WHERE from_date < '2002-08-01' and from_date > '2002-07-01'

score 1 · Accepted Answer · answered Feb 17 '17 at 17:45

1

It looks like you need to put new_hires.csv inside single quotes ('new_hires.csv').

Also, I think the order of your statements needs to put the FROM and WHERE clauses at the end. Give this a try:

SELECT from_date INTO OUTFILE 'new_hires.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM dept_emp_latest_date
WHERE from_date < '2002-08-01' AND from_date > '2002-07-01';

answered Feb 17 '17 at 17:45

UnseenSpecter

68
8

That seemed to fix the syntax error! Thank you very much! – Leslie Tate Feb 17 '17 at 18:43
Im now prompted with the error: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement – Leslie Tate Feb 17 '17 at 18:44
1

[Take a look at this thread](http://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql). It explain why the error pops up for a similar problem and how to resolve it. – UnseenSpecter Feb 17 '17 at 19:01
Thank you @UnseenSpecter – Leslie Tate Feb 17 '17 at 19:20

SQL Create Queries And Redirect To File

1 Answers1