From: Michael B Allen [mailto:[EMAIL PROTECTED]
> I'd like to move some bookmarks in one database to another. The format > of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly > trucated): > > +------------------+------------------+------+-----+---------+ > -----------+ > | Field | Type | Null | Key | Default > | Extra | > +------------------+------------------+------+-----+---------+ > -----------+ > | bookmark_id | int(11) unsigned | | PRI | NULL > | auto_incr | > | group_id | int(10) unsigned | | MUL | 0 > | | > | bookmark_title | varchar(255) | | | > | | > | bookmark_url | varchar(255) | | | > | | > | bookmark_descrip | varchar(255) | YES | | NULL > | | > | bookmark_creatio | datetime | | | 0000- > | | > | bookmark_private | char(1) | | | 0 > | | > | bookmark_last_hi | datetime | YES | | NULL > | | > | user_id | int(10) unsigned | | MUL | 0 > | | > | bookmark_deleted | char(1) | | | 0 > | | > +------------------+------------------+------+-----+---------+ > -----------+ > > I want to move this into the online-bookmarks 'bookmark' table which > has the format: > > +-------------+---------------+------+-----+---------+-------- > --------+ > | Field | Type | Null | Key | Default | Extra > | > +-------------+---------------+------+-----+---------+-------- > --------+ > | user | char(20) | | | | > | > | title | char(70) | | MUL | | > | > | url | char(200) | | | | > | > | description | char(200) | YES | | NULL | > | > | private | enum('0','1') | | | 0 | > | > | date | timestamp(14) | YES | | NULL | > | > | childof | int(11) | | | 0 | > | > | id | int(11) | | PRI | NULL | > auto_increment | > | deleted | enum('0','1') | | | 0 | > | > +-------------+---------------+------+-----+---------+-------- > --------+ > > The only field mappings I care about are: > > bookmark_title -> title > bookmark_url -> url > bookmark_description -> description > > Now I know I can generate insert statements and then run that > script on > the target but is there a better way? The databases do not have direct > access as each is running on localhost only. While it's a little clunky, why not do this on the first server: CREATE TABLE apb_bookmarks_tmp SELECT '' AS user, bookmark_title AS title, bookmark_url AS url, bookmark_description AS description, 0 AS private, NULL AS date, 0 AS childof, NULL AS id, 0 AS deleted FROM apb_bookmarks; I'm assuming that by "these are the only field mappings that I care about," you mean that those are the only fields you want brought over. If not, then replace the NULLs and 0s with the proper field names. Once you've done this, all you need to do is mysqldump the table, scp it over the the new host, and import it. If you had indices on the original table, you'll have to manually create them on the new table -- CREATE TABLE ... SELECT FROM doesn't carry those over. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]