Accessing aliased tables

Question

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 

Answer 1

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.

Answer 2

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) 

Answer 3

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
)