DROP TABLE IF EXISTS tmp_albums; # create the lookup table CREATE TABLE tmp_albums (album_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, artist varchar(20) not null, album varchar(30) not null, genre varchar(10) not null, KEY artist (artist), KEY album (album), KEY genre (genre)); # populate the lookup table INSERT INTO tmp_albums SELECT DISTINCT NULL, artist,album,genre FROM MP3; DROP TABLE IF EXISTS songs; # create the destination table CREATE TABLE songs (ID int(11) not null auto_increment, title varchar(40) not null, duration time not null default '00:00:00', size int(11) not null, album_id INT(11) NOT NULL, PRIMARY KEY (ID), KEY title (title), KEY album_id (album_id)); # Here is the trick! Using the lookup fields # as foreign keys, we populate the destination # table from source_table JOINed to lookup_table INSERT INTO songs SELECT src.ID, src.title, src.duration, src.size, album_id FROM MP3 src INNER JOIN tmp_albums lkp ON (src.artist =lkp.artist and src.album =lkp.album and src.genre =lkp.genre);