Subscribe to RSS
Subscribe by email
If you ever need to export certain records from a MySQL table in order to import them into another table in a different (or even the same) database (say you want to export some records from your old blog to a new one), then you should simply follow these 3 steps:
- Export the records from one database into a file, using the following SQL query:
SELECT * INTO OUTFILE '/folder/filename.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name WHERE .... ;
You can find more about this statement from MySQL site. - Import the file’s content to another database table, using the LOAD query:
LOAD DATA INFILE '/folder/filename.sql' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
Please, note that when loading the data you should specify exactly the same delimiters you’ve used earlier in the SELECT statement. Read more about it from this MySQL page. - Finally, in most cases, if you’ve exported all records using SELECT * statement, then it will export ALL auto_incremented IDs from that table as well, which may set off the auto_increment order in the imported table. For example, perhaps you’ve exported all records from ID=125 to ID=151, but the imported table originally had only a few records, say only until ID=7. All new records that you’ll be inserting to this table will start from ID=151, and you’ll end up having this big gap between ID=7 to ID=125. Thus, you need to ALTER the IDs and auto_increment settings in the imported table, with the following two steps:
UPDATE table_name SET ID_column=ID_column-N WHERE ...;
Where N is a number that’ll close the gap between the old IDs and the newly imported ones. Just make sure you specify a unique constraint in the WHERE statement that will identify all newly imported records (usually, it’s the same WHERE constraint you’ve used when Exporting your records).After that you need to update the auto_incrementation of this table using:
ALTER TABLE table_name AUTO_INCREMENT=N;
Where N is the last number of your ID_column after the update
Just make sure that you have the right permissions set up on the server to create a new file and load from it. Hope this all makes sense and somebody will find it useful.
No Intelligent Bits in response to “Exporting / importing database tables”
Sorry, comments are closed on posts older than 90 days.