Export table in mysql using sql query

Hello,

I am using MySQL Workbench 5.2 OSS to view and when necessary, remove records from a table via SQL Statements.

I am trying to run the statement below to export all records from a table named master_rate_records as a text file with the same name, but it is telling me there is an error in my SQL syntax (error code 1604).

I've tried to reword it a few times with no luck. I figured it was time to ask the experts.

Thanks in advance!
SELECT * FROM master_rate_records
  INTO OUTFILE 'master_rate_records.txt'
          FIELDS TERMINATED BY ','
          ENCLOSED BY '"'
          LINES TERMINATED BY '\n'

Open in new window

McFoxxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Okay, I am not losing my mind. If you go to Edit --> Preferences, click on SQL Editor tab of Workbench Preferences, you will see a section towards the bottom called Query Results. The first check box in this section is Limit Rows and it was checked by default on my installation with a row count of 1000 so you should see something similar.  

Uncheck the limit rows box or arbitrarily increase the 1000 to some other value.
0
 
McFoxxAuthor Commented:
I tried the following and it was able to export a file.... however I was expecting something arounf the range of 100MB, I only got a 70K file with only the first 1000 lines. Is this some sort of limit I can bypass?

Thanks in advance!
SELECT *
INTO OUTFILE 'C:\\master_rate_records.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM master_rate_records

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
None that I know of (limits on OUTFILE), but I did notice after loading the new Workbench there is a limit to the rows returned ... let me see if I can track down where to change it.
0
 
McFoxxAuthor Commented:
mwvisa1, thanks a ton!

It worked, the query has been running for a few minutes now, so I know it's doing it's thing.

Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.