Copy info between database tables with one query

I was thinking about the future of my web site the other day. One of the questions I began to think about was, what if I decide to change to a different Content Management System (CMS)? Which was never really a problem before because I did not have much content to move over. In the past I had pages for my portfolio, about me, contacting me, and that is about it. Now that I have begun to write on my site as well, I now have to worry about how to preserve the content. The two areas I am concerned with are:

• Moving data from one database to another.
• How to handle URL rewriting so that my pages don't suddenly go AWOL in search engines.

Set-up

Say you have an existing table in a database called DB1,

CREATE TABLE `Event_Info` (
  `Event_ID` int(11) NOT NULL auto_increment,
  `Event_Type_ID` int(11) NOT NULL,
  `Event_Title` varchar(255) NOT NULL,
  `Event_Date` varchar(100) NOT NULL,
  `Event_Address` varchar(100) NOT NULL,
  `Event_Town` varchar(100) NOT NULL,
  `Event_Desc` longtext NOT NULL,
  `Event_Inserted` varchar(100) NOT NULL,
  `Event_Updated` varchar(100) NOT NULL,
  `User_ID` int(11) NOT NULL,
  `Event_Pending` varchar(255) NOT NULL default '0',
  PRIMARY KEY  (`Event_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;

You are tasked with moving over the id, title, town, and description to a new database/table to be used with a different application. I put together this test table to move the information into, it is located in a database named DB2.

CREATE TABLE `event_stuff` (
  `eventID` int(11) NOT NULL auto_increment,
  `eventTitle` varchar(255) NOT NULL,
  `eventTown` varchar(255) NOT NULL,
  `eventDesc` varchar(255) NOT NULL,
  PRIMARY KEY  (`eventID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Moving the data

So what we need to do is select the title, town, and description from Event_Info and insert it into event_stuff. The first step is to write a query that selects the data from Event_Info. In the past when I have transferred info between two tables I have used a subquery to get the values to insert. Something along the lines of this, INSERT INTO Table_One VALUES(SELECT field1,field2,field3 FROM Table_Two). I tried to set something like this up and ran into one problem, I am working not between tables but between databases.

The query

After reading some documentation on the MySQL site and spending a couple hours working out queries. I figured that I must need to reference the database somehow but I really could not find adequate information about it and so I finally gave in and went to Stackoverflow. There I found out that I didn't need the schema files as I thought I might have and all I needed to do was reference the database. The final query that worked flawlessly for me was

INSERT INTO DB2.event_stuff (eventTitle,eventTown,eventDesc) 
SELECT t.Event_Title, t.Event_Town, t.Event_Desc FROM DB1.Event_Info t;

Something I learned from this query is that you can set an alias for a table after you use it! I had always been under the impression it was like PHP, PERL, ASP, etc... in that you needed to instantiate the variable before using it. I wonder if MySQL reads the whole query before executing it? Anyway, if you have any input let me know, if there is a more efficient way to do this with one query I would be interested to see that.

Part two will cover URL rewriting so there isn't a loss in ranking with the search engines.