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

Pat

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

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

thanx a lot!