Tuesday, June 3, 2008

SQL command to dump data into file and load data from file

In order to migrate data, it is useful to dump data from database table to a file, and load data from file to a table.

To dump data for a specific range in a file, you can use the following SQL command:

SELECT $fields INTO OUTFILE '$dump_file_name' FIELDS TERMINATED BY "\t"
ENCLOSED BY '"' ESCAPED BY "\\" LINES TERMINATED BY '\n' FROM $table_name WHERE
id >= $start AND id < $end;

You can then load the file by using the following SQL command:

LOAD DATA INFILE '$dump_file_name' INTO TABLE $table_name FIELDS TERMINATED BY "\t"
ENCLOSED BY '"' ESCAPED BY "\\" LINES TERMINATED BY '\n' ($fields);


These two commands work fine with MySQL.

No comments: