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.
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.
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.