How to insert data in to table with a Union

Question

I'm trying to insert more data in to my tables, This code below works perfectly:

insert into GENRES (GENRES) 
SELECT GENRE1 FROM titles
union all
SELECT GENRE2 FROM titles
union all
SELECT GENRE3 FROM titles

However, when i try to amend it so I can insert the MOVIE_TITLE_ID with it I can an error:

insert into GENRES (GENRES,MOVIE_TITLE_ID) 
SELECT GENRE1 FROM titles
union all
SELECT GENRE2 FROM titles
union all
SELECT GENRE3 FROM titles
union all
select TITLE_ID
from titles;

Error MSG:

Error starting at line : 175 in command -
insert into GENRES (GENRES,MOVIE_TITLE_ID) 
SELECT GENRE1 FROM titles
union all
SELECT GENRE2 FROM titles
union all
SELECT GENRE3 FROM titles
union all
select TITLE_ID
from titles
Error at Command Line : 175 Column : 13
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 -  "not enough values"
*Cause:    
*Action:

I'm guessing I'm doing it in the wrong format, Any help will be useful!

Answer 1

Each of the unioned subquery must return the same number of columns, with equivalent datatypes, which, in turn, must match the column list for insert. Your query declares two columns for insert, but the subqueries return just one column (and, possibly, the datatype of title_id is not the same as genre1).

Instead, each subquery should produce two columns: the genre, and the related title id:

insert into genres (genres,movie_title_id) 
select genre1, title_id from titles
union all
select genre2, title_id from titles
union all
select genre3, title_id from titles