Subscribe!
RSS
  • Moshu
  • Prairie
  • WordPress
  • The combined blog: about WordPress, the Canadian Prairies and myself
    The Journey to full time blogging


Moving several posts from one blog to another

I decided to move over here all my WP related posts from my “cross-cultural” blog, so the task was to extract (export) a few posts and their comments and to insert/import them into the database of this blog.
This post is a follow-up to my question posted in the WP forum.

As I was advised I used the phpMyAdmin offered by my host to perform this operation. Actually it turned out to be easier than expected. Here are the steps I have followed:

  1. Being in the proper database click on the wp_posts table name in the left panel (your tables might have a different prefix, this is just an example!)
  2. In the next panel click Browse on the top
  3. Select the the checkbox at the beginning of the rows that you want to export (for the sake of simplicity I’ll describe it here as exporting 1 single post=row)
  4. At the bottom of the rows where it says “With selected” – select “Export”
  5. On the next panel deselect Structure; leave selected Data, and check Save as file, then GO
  6. You will get a prefix_posts.sql file
  7. Open the file in your favourite text editor (definitely not MS Word); I used Notepad++
  8. Using Search/Replace change the wp_ part everywhere to target_ – depending on the prefix of the tables where you want to move the post
  9. If you move the post to a new, empty database, you are ready to go. However if the target blog has already posts in its database, you have some more work to do:
    • — check in the database what is the ID# of your last post in the target blog
    • — Go back to the .sql file and take a look at the first line below Dumping data for table.... It should be something like:
    INSERT INTO `target_posts` VALUES (34, 1, '2005-03-31 03:13:10', ...
    • — That very first number after the VALUE is the post ID#. Change it to your last post ID#+1, i.e. if the last post in your target blog has ID# 53, make it 54. Save the file.

      Warning! You can chose any number, even 1,000 – but be aware that your next post made in the usual way from the admin Write Post panel will have 1001 as ID# in this case!
  10. Now go the the DB of the target blog, and select the target_posts table
  11. From the menu at the top select SQL
  12. On the next page select Location of the textfile: Browse and find the changed and saved .sql file on your computer; click Open and, when the path is displayed in the field, click GO
  13. If you are lucky, phpMyAdmin will report a successful action. The post is in your new/target blog. If you are concerned about the order where it appears, you can simply go to your blog admin panel and change the time stamp.
  14. Possible problems. If you are importing from a DB that was created by earlier WP versions, you may encounter an "EROOR #1136 - Column count doesn't match value count at row 1". I got this error due to the two columns in the posts table: ‘post_lat’ and ‘post_lon’ that were there for the geo-location in WP 1.2 and earlier versions (see my post in the forum)
  15. What I did: Checked the .sql file and found the two 'NULL', 'NULL' values after the text portion of the post (it was NULL since I’ve never used that feature) and simply deleted them. Now the number of columns was the same, and the import successful.
  16. Another issue and warning. Be aware that some plugins create their own table in the WP database and this can cause the same Error as above. Check and compare the two databases and if there is any column created by a plugin, you have two choices:
    • — install the very same plugin into the DB that is missing it
    • — delete the values referring to that column from the the source .sql file
  17. When you are done just do the import (SQL) query

With comments the procedure is very similar. For the post moved above I checked “on the surface” and saw there were 7 comments. Knowing that the post ID# used to be 34 I checked the wp_comments table in Browse mode to find the comments attached to that post. Checked all the 7 rows and exported them.
Next, opened the wp_comments.sql file in the text editor and

  1. changed the table prefix (see above)
  2. changed the post ID in the comments

For this latter you have to look at the .sql file and you can see for every comment exported there is a line starting like:

INSERT INTO `target_comments` VALUES (69, 37, 'Moshu',...etc.
INSERT INTO `target_comments` VALUES (71, 37, 'John Doe',...etc.

Search/Replace “37″ with the post’s new ID# – which in my case was 54, see above. The first number is the comment’s own ID#.
If done, import it via the SQL query as we did with the post.
Warning: There might be error messages if the comment ID# is already in use. Just go back to editing, change the number to something that probably doesn’t exist, and try again the query.

I am sure there might be some other way to do this, especially if you are “fluent” in MySQL commands, but for those less skilled it can be helpful.


29 Responses

  • Bhaskar ·

    sorry i am not clear please explain me clearly.
    for exmple :
    If i have two databases which are in two differnt domains and the tables are also the same in both the domains and also the data in the tables may be same.some data may be differnt ? how to do in such cases please help me …..

  • Shay ·

    This will definetly help me if/when I decide to move. Thanks

  • Will ·

    Thanks very much for taking the time to post the tips and “how-to’s”. There are few sites with as much concise, useful WordPress info. Those of us who are learning as we go find it invaluable.

  • Brian ·

    I’ve used your page twice now when consolidating multiple blogs. Very useful. Perhaps these instructions should be made available in the WordPress Codex???

  • Axel ·

    Hi moshu, as I said on the forum, thanks for the handy guide. Sadly I saw it after I posted a similar yet somewhat less comprehensive howto on my missus blog.

  • Belle ·

    Thank you so much for the tutorial! It took several hours and a lot of concentration, but thanks to you I was able to move some posts that I wanted to keep, but which no longer fit in with the direction of my site. Please submit this to the Codex, it was a big help!

  • krome.obsession ·

    Thank you so bloody much!!!

    I had made two backups of my blog, but when it came to the cruch they just didn’t seem to work .. I kept getting errors trying to install them.

    But thanks to this I was able to pick out the posts and comments and put them in.

    THANKS!!!

  • beachbum ·

    I have 2 blogs, domain1.com with many catagories and comments, and domain2.com with one catagory and no comments. I want to have 1 of domain1.com’s catagories be domain2.com’s 1 catagory blog.

    Is this kinda the same? How would I do this?

    Welp!!

  • Maria ·

    Thank you for an excellent explanation that helped me a great deal when I needed to fill my development blog with material from the original blog (swedish foodblog).

  • John Walsh ·

    I’m glad I came accross this site as it was the exact info I was looking for.

    You have saved me much time and effort.

  • RyanG ·

    Wonderful. Thank you. The last time I changed databases I “had” to hand transfer each file — like cut and paste and backdate. So I appreciate your smartness.

    And for your records, my old database was using wordpress 2.0, the new one 2.0.5 — no problems there. I just had to add a plug-in and change the ID of a couple static pages.

  • Mike Paetzold ·

    Wish I had found this post a few weeks ago when I was converting a blog to a new one.

    Well it is bookmarked now.

  • mandee ·

    Hi this tutorial was very helpful. Everything worked out good but for some reason my comments are only showing up in the admin login area but not on my blog site. my blogs that i imported say i have (#of comments) but when i click to read them on my site they don’t show up. =[

  • Moshu ·

    Did you try to switch to the default or classic theme? Sometimes, problems like this could be theme-related. Other than that I don’t know what to say – since I don’t have access to your site…

  • Andrew ·

    THANK YOU.
    This was great although I think step 11 should say “From the menu at the top select IMPORT”

    At least that’s what it should have been on my version of phpMyAdmin.

    thanks
    Andrew

  • Hassaan Khan ·

    HI,
    Im a new blogger. I write different blogs from political and business blogs to crack softwares blogs.
    I need a favor from you. “I want to know that how can i have same posting on two wordpress blogs with some remote posting. Like, i can use same database, I tried so much, but could not do. I want actually to post same posting of wordpress blog 1 to wordpress blog2. . .
    Whatever i post on blog1 should be posted to 2nd wordpress blog of mine.
    Please help me !
    I’ll be thankful to you!

    Hassaan Khan
    blogger
    [URLs deleted by blog owner]

  • Raize ·

    Thanks

  • Blog Builder ·

    Ok so it took me few years to find the post. With all the updates that have been made is the post still revelant or would changes in the procedure need to be made?

  • Moshu ·

    To be honest I didn’t need to do this for quite a while… which means I didn’t test it on newer versions of WP.

    The only problem that I can think of would be the taxonomy tables: as you may know, since v2.3 a new taxonomy schema was introduced with 3 new tables in the database. It keeps the data regarding your categories, posts, tags and – most importantly – the relations between them.

    In newer versions of WP (where we have the Export/Import function) I would go like this: create a new, temporary user. Assign the posts to be moved to this author. Go to Tools > Export and select the new user. Do the Export. Then move to the other blog and import the downloaded file.

  • ciprian ·

    Very cool. I just moved a few posts from one blog to another using your indications. Worked very well. I also moved the wp_postmeta along with all the images.

    Thank you!

  • Ravi ·

    WordPress has tools in the admin area to export posts and/or pages from one site to another site. You don’t really need to deal with the database directly. See details here http://wordpress.org/support/topic/blog-posts-transfer-from-one-installation-to-another

  • Moshu ·

    Ravi, my dear, are you really this retarded or you just pretending?

    Did you take a look at the date of this post? – June 2005!!!
    I bet at that time you had no idea WordPress existed…

    WTF, you want to teach me WordPress? Go here: http://wordpress.org/support/profile/moshu
    While being on that page notice the number of posts shown on a page (30). Now look very carefully at the number of pages: 751.
    751 x 30 = 22,530… but I’d say 22 thousand is a nice round number. That’s the number of replies posted by me to help people with their WP related issues during several years.

    Have fun!

  • Colin ·

    Just as a follow up to the above two posts, I’ve just had to move posts/comments/stuff from a borked database (no access from WP but could see it in MySQL). [I guess the borking was in the wp-options bit of the database.]

    Total nightmare but this post STILL helped me loads even in 2013. So thanks Moshu!!! A couple of details in phpMyAdmin have changed but the workflow was still v relevant.

    And I promise to do full XML backups from now on before I get into trouble again – was content to do rolling database dumps before!

6 Trackbacks/Pingbacks

  1. Le blog » Archive du blog » R�cup�ration de vieux messages
  2. Le blog
  3. Lydia Finch's Blog
  4. ::- Spider Lake -:: » Blog Archive » Administrative Notice
  5. Poof be gone at Sugar Vamp
  6. Poof be gone at Sugar Vamp

RSS feed for comments on this post.   TrackBack URI

Leave a Reply

Do something different
with your WordPress site!

Build SILOS and get in Google's Goodbooks
Here is how to make them LOVING your site:

Silo Manual

GET YOUR FREE REPORT NOW!
(click on the silos!)

Get the free siloing manual
and beat your competition today!




  • Make and save money!
Hassle-free blog setup in minutes   Click here to join Easy Video Producer
RAP Bank   Text Link Ads