Heal Your Church WebSite


Teaching, rebuking, correcting & training in righteous web design.

Moving Data from one Database to Another with PHPAdmin

Moving from one blog to another can be a real pain. Especially if you don’t want to write a conversion program, or are limited to using a tool such as phpMyAdmin.

So here’s what we’re going to do. We’re going to move data from table to another, via a third interim table. Why? The interim table gives us the opportunity to make changes both to the data and/or the table structure without fear of destroying production dasta. Of course it doesn’t hurt to have a real backup as well. To do this, selected the destination table within the destination database using phpAdmin. Once the table was selected, I choose the “Operations” tab/screen and copied the structure to the source database.

At this point, I had within my Nucleus database an interim table named item2mt, which was based upon the structure of the mt_entry table in my MovableType database. Now to move the data from the source table into the interim table. This mean using the following SQL construct:

INSERT into `item2mt` SELECT inumber, 1, 2, iauthor, 0, 0, ‘__default__’, icat, ititle, ”, ibody, imore, ”, null, null, null, itime, itime, 1, 1 from `nucleus_item`;

The trick here is to make sure that the datatypes and number of columns (fields) after the keyword SELECT match that of the iterim table.
Once this is done, you can apply any host of SQL queries to massage the data to taste. For example:

UPDATE `item2mt` SET icat = 7 WHERE icat = 9;

You can then use phpAdmin’s Export feature, or once again use the “Operations” screen, to get data over to the destination table. Of course, if the interim table is either in, or moved/copied to the destination database, then one only need to run the following SQL to get the job done:

INSERT into `mt_entry` SELECT * FROM `item2mt`;

Yes, I know this is a very geek article, but data conversion happens. Since some of you aren’t code monkies, I figured I’d give you some pointers on how to use a common (and free) tool such as phpMyAdmin to get the job done.

Now for those of you who feel like living dangerously. Here’s how you can do it from table-to-table from the confines of a single database (to another database). All you need is the structure of the source table via the phpAdmin Export features, then …



Of course, if you’re a SQL-stud, you can forego any saftey nets you might gain from an interim table and just table-to-table, database-to-database using the following syntax:


Don’t forget to rebuild. And it might not hurt to back things up first.

UPDATE – MovableType has a table called mt_placement which hooks-up entries with their categories. If you’ve populated an entry table with any of the above methods, than the following query will get you the rest of the way there:

INSERT INTO `mt_placement` SELECT entry_id, entry_id, entry_blog_id, entry_category_id, 1 FROM `mt_entry`

5 Comments

  1. Mr. Peters,

    Yesterday, I posted a comment at Josh’s site; it has to do with one of your recent Blogs4God entries regarding adding an RSS feed to our sites, something that I was looking into doing, when I noticed that entry from March 12th. I explained everything in that comment post at JC.com – I was wondering what I should do next, now that I have added the RSS feed…. (as pertaining to your relevant post).

  2. Sorry, I used an embedded hyperlink in that above comment. My post at JC.com from yesterday is here:

    http://www.joshclaybourn.com/cgi-bin/mt/mt-comments.cgi?entry_id=748

    Thanks!

  3. Aw geez, Dean, I just went through this moving my blog from one machine to another, and that was Movable Type to Movable Type! Reading this post was like deja-vu!! See http://www.eal.us/archives/000124.html

  4. Hello Guys!!
    I need to insert records of table1 from db1 to table1 of db2. Kindly suggest the safest way to do that. I tried doing it but was stuck some times because of IDENTITY_INSERT PROBLEMS OR OTHER.
    Kindly Suggest!!

  5. Thanks for your valuable information.