Putting one SQL table on top of another using oracle SQL-CodePudding

This is probably a very simple question but I cannot find the solution. I have two tables with identical column names and I wish to put one on top of the other. I have tried UNION but this appears not to work and I get the error 'ORA-01790: expression must have same datatype as corresponding expression'

I am using Oracle SQL Developer to access the data.

table1 =

column1	column2	column3
1111111	2222222	3333333
aaaaaaa	bbbbbbb	ccccccc

table2 =

column1	column2	column3
9999999	8888888	7777777
zzzzzzz	yyyyyyy	xxxxxxx

desired output

column1	column2	column3
1111111	2222222	3333333
aaaaaaa	bbbbbbb	ccccccc
9999999	8888888	7777777
zzzzzzz	yyyyyyy	xxxxxxx

I have tried the following script to get it - any assistance would be appreciated.

select * from table1
union
select * from table2

CodePudding user response：

The data type problem might be fixed by explicitly listing out all columns in the select clause. In addition, you should introduce a computed column which maintains the order of the two halves of the union in the output.

SELECT column1, column2, column3
FROM
(
    SELECT column1, column2, column3, 1 AS src
    FROM table1
    UNION ALL
    SELECT column1, column2, column3, 2
    FROM table2
) t
ORDER BY src;