need to transfer data from old php like db to rails style db

hey all, I have two tables like that from an old php project: artists(id,name) albums(id,artist_id,album_name)

and I need to transfer the data of this database to three tables that look like this: artists(id,name) albums(id,name) artists_albums(album_id,artist_id)

any idea what's the fastest query to do this?

thanx in advance


Well, for starters, you probably want albums_artists if you intend this to be a has_and_belongs_to_many table since "albums" comes before "artists"

rename artists to old_artists rename albums to old_albums

insert into artists select * from old_artists insert into albums select id, album_name as name from old_albums insert into albums_artists select id as album_id, artist_id from old_albums

then if all your test pass :wink: you can drop the old_* tables

You didn't say what database so I'm leaving this generic. (and migrations are great for this sort of thing, too)


Rob Biedenharn

thanx a lot!