This question is wrong. I had some very big misunderstanding about how union works. I am reading about it now.
edit 04.12.2016
If you are still intersted, you can go here
Selecting the right column
I have something like this
with table3 as
(
select t1.c1, t1.c2...
from table1 t1
union all
select t2.c1, t2.c2...
from table2 t2
)select * from table3
I need to insert all rows from above in another table
insert into table4 t4
(
t4.c1, t4.c2...
)
select t3.c1, t3.c2...
from table3 t3
My question is, will this insert work. I have clumns in table 1 and 2 named the same, will I need to reference them somehow differently?
Do I need to write it like this?
insert into table4 t4
(
t4.c1, t4.c2...
)
select t3.t1.c1, t3.t1.c2, t3.t2.c1...
from table3 t3
No alias needed
if the column match you could simply use insert select
insert into table4
( select t1.c1, t1.c2...
from table1 t1
union all
select t2.c1, t2.c2...
from table2 t2)
otherwise you should declare the column name
insert insert into table4(c1, c2... )
( select t1.c1, t1.c2...
from table1 t1
union all
select t2.c1, t2.c2...
from table2 t2)
with is part of select statement. You can insert result of select and you can use with in this select. Maybe syntax is not the most intuitive but this should work:
insert into table4
with table3 as
(
select t1.c1, t1.c2...
from table1 t1
union all
select t2.c1, t2.c2...
from table2 t2
) select * from table3;
And no you don't need (even can't) use double aliases.
Assuming that you needto use that UNION ALL, instead of single insert-as-select statements to insert into another table, you can try to use different aliases for columns from different tables:
with table1 as
(
select t2.name as t2_name,
t2.address as t2_address,
t2.age as t2_age,
null as t3_name,
null as t3_address,
null as t3_age,
from table2 t2
union all
select null,
null,
null,
t3.name,
t3.address,
t3.age
from table3 t3
)
