How to create table from an existing table

Question

I'm in Oracle APEX and would like to create a new table from other existing one like this:

I have a column in a SONGS table that is:

ARTIST NAME	Another header
Bad Bunny	row
Bad Bunny, Ozuna, Daddy Yankee	row

And I want this in another table:

ID	Artist
1	Bad Bunny
2	Ozuna
3	Daddy Yankee

Also, from these 2 tables I would like to create another table or relationship to indicate on what songs have an artist participated.

Something like:

Song ID	Artist ID
1	1
2	1
2	2

I want this third table to know in what song has participated an artist.

So im having troubles to create the 2nd and 3rd table from the first table.

Please don’t link to images in your question. Add the definitions of the tables you have and the ones you want to your question, as editable text. Also update your question to show the logic needed to create your target tables - at a minimum provide some sample data and the result you want to achieve based on that data — NickW, Dec 05 '21 at 15:19
There are some design problems that would be advisable to address before attempting SQL queries. The first one is not a normalised table, it doesn't even have a key. The 3rd one is showing the same artist with two artist ids. — Jayvee, Dec 05 '21 at 15:50
The 3rd table should just have IDs (plus any attributes that are specific to the song/artist relationship). You should not be duplicating data between tables - read up on normalisation, specifically the 3rd normal form — NickW, Dec 05 '21 at 16:48

score 0 · Accepted Answer · answered Dec 06 '21 at 12:06

These are actually 2 different issues in a single question. Treat them as 2 different issues.

How to design your tables. This needs to be done first, you can import your data in them later. Create a songs table, an artist table and an intersect table (artist_songs) with foreign keys to both artists and songs. It should be pretty straightforward, there are thousands of examples on the web or, since you're using apex, using quicksql (SQL WOrkshop > Utilities > quick sql) for generating the tables is also an option.
Migrate the data from that base table into your new, normalized tables. Have a look at this similar stackoverflow question

You have provided very little info about what data you have available initially. It only shows artists in a comma separated form - it's hard to say how you would populate the songs or intersect table with that info.

score 0 · Answer 2 · answered Dec 07 '21 at 11:35

You can write and execute a procedure such as:

create or replace procedure "P_SONG_PARSE"
is
my_artist VARCHAR2(4000);
my_artist_id NUMBER;
my_temp VARCHAR2(4000);
my_pos NUMBER;

begin
    for rs in (select * from songs)
    loop
        my_temp := rs.ARTIST_NAME;
        my_pos := 0;
        loop
            my_pos := instr(my_temp,',');
            if my_pos = 0 then
                my_artist := trim(my_temp);
            else
                my_artist := trim(substr(my_temp,1,my_pos-1));
            end if;
            begin
                select ID
                into my_artist_id 
                from ARTISTS
                where ARTIST = my_artist;
                exception 
                    when no_data_found then
                        begin
                            select nvl(max(ID),0) + 1
                              into my_artist_id
                              from ARTISTS;
                            insert into ARTISTS(ID,           Artist)
                                        values (my_artist_id, my_artist);
                        end;
            end; 
            insert into SONG_ARTISTS("Song ID", "Artist ID")
                              values(rs.ID,     my_artist_id);
            exit when my_pos = 0;
            my_temp := substr(my_temp,my_pos+1);
        end loop;
    end loop;    
end;

How to create table from an existing table

2 Answers2