All Things Techie With Huge, Unstructured, Intuitive Leaps

Mysql infile and outfile, moving data

Well, I had to use these commands again, so I thought that I would refresh yours and my memory.  I had to take messages from one database and transfer them to another.  The easiest way is to dump the necessary rows into a csv file and read them back in to the other database.

Here are the commands:

To copy the data to a file:

SELECT senderId, 
   senderName,
   messageBody,
   showOrNot,
   meId, 
   req,
   whens,
   co  
  INTO OUTFILE '/tmp/msg.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM messagetable
  LIMIT 100;

To read it back in using mysql command line:

LOAD DATA LOCAL INFILE 'msg.csv' INTO TABLE messagetable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

Hope this helps someone.

No comments:

Post a Comment