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:

  1. 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.
  2. 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.
  3. 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.

Recent Bits
Related Bits
The Secret Truth About The Plugins Security
The Best WordPress Plugin Ever
Contrasting Approaches To Web Design
8 Finest Bits Of Free Stock Photo Sites
8 Security Bits
5 Bits About Design And Me
8 Worthwhile Weekly Bits of Information – Part 1
A Bit Of Literature – You Were Perfectly Fine
Comment Bits

No Intelligent Bits in response to “Exporting / importing database tables”

Sorry, comments are closed on posts older than 90 days.

Selected Bits

PopularBits

RecommendedBits

FavouriteBits

PersonalBits

Hi, I'm Vivien. Thanks for visiting my Inspiration Bit. I often find myself scouring the internet looking for either answers to many questions I have or websites that inspire me, sites that I can learn from. On what topics you might ask — any topics that interest me, anything from web design to typography and art, from blogging to entrepreneurship, from programming to open source.
read more…
When I'm not blogging, I design web sites, teach, play with my daughter and try to balance family, work, friends and a somewhat active social life on