$5
What select INTO OUTFILE syntax gives me output like mysqldump?

If I run mysqldump on a table, I get an sql file that I can then import in another database. But I do not want to copy over an entire table. I want to copy over only a few thousand records. So I will use select INTO OUTFILE to create a file with the info that I want to transfer. But I am having trouble figuring out how to format that outfile. Getting it into CSV format is, but that is not what I want. Instead, I want INSERT statements that I can run on the new database. How do I achieve that?

Lawrence Krubner | 07/19/10 at 3:00pm | Edit

into outfile

(2) Possible Answers Submitted...

Last edited:
07/19/10
3:17pm
William Clark says:
Hi Lawrence,

Try this:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

Then to import that csv, do
LOAD DATA LOCAL INFILE '/tmp/result.txt'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, field2, field3);

Make field1, field2, field3, etc. your column names.

Hope this helps!
Bill

Previous versions of this answer: 07/19/10 at 3:17pm | 07/19/10 at 3:17pm
- 07/19/10 3:29pm
  
  Lawrence Krubner says:
  That is exactly what I am hoping to avoid.
- 07/19/10 3:30pm
  
  William Clark says:
- 07/19/10 3:36pm
  
  William Clark says:
  Add a where clause to the SELECT query I suggested or use a LIMIT constraint.
  
  ie:
  
  SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table
  WHERE field1 = "stuff";
  
  or
  SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table LIMIT 1000;
  
  Lawrence has also suggested a good way to do this.
- 07/19/10 3:36pm
  
  William Clark says:
  Add a where clause to the SELECT query I suggested or use a LIMIT constraint.
  
  ie:
  
  SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table
  WHERE field1 = "stuff";
  
  or
  SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table LIMIT 1000;
  
  Lawrence has also suggested a good way to do this.
Last edited:
07/19/10
3:31pm
Steven Siebert says:
Hi Lawrence,

If you want them to come out with insert statements, you need to use mysqldump instead of select into outfile. mysqldump (run from the command line) has the ability to use a "--where" argument, to limit the statements you're using. For more info, see: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_where

You can still use select into outfile if the command line is not an option. Continue to dump as CSV and use the LOAD DATA INFILE command to import it. For more info, see: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Let me know if you have any issues.

Steve
- 07/19/10 3:31pm
  
  Lawrence Krubner says:
  Nice! Funny, I read that page and I never noticed the bit about the where clause.