Select multiple tables with same column-CodePudding

I have two tables, with the same structure of columns. The first table has data from 2019. And now I want to add date from 2020 and later from 2021. How can I do this? Union, but how?

SELECT cast(cast( Replace(xy,',','.') as float) as datetime) xy
, [xy1]
, cast(xy2 as int) xy2
, [xy3]
, cast( Replace(xy4,',','.') as float) xy4
, [xy5]
, cast( Replace(xy6,',','.') as float) xy6
, cast( Replace([xy7],',','.') as float) [xy7]
, cast( Replace([xy8],',','.') as float) [xy8]
, cast( Replace([xy9],',','.') as float) [xy9]
, [xy10]
FROM 2019
WHERE xy7 not like '#%'

Both table have this structure.

2019
x| xy1| xy2 | xy3 etc etc etc
2020
x| xy1| xy2 | xy3 etc etc etc

CodePudding user response：

You can do:

SELECT cast(cast( Replace(xy,',','.') as float) as datetime) xy
, [xy1]
, cast(xy2 as int) xy2
, [xy3]
, cast( Replace(xy4,',','.') as float) xy4
, [xy5]
, cast( Replace(xy6,',','.') as float) xy6
, cast( Replace([xy7],',','.') as float) [xy7]
, cast( Replace([xy8],',','.') as float) [xy8]
, cast( Replace([xy9],',','.') as float) [xy9]
, [xy10]
FROM 2019
WHERE xy7 not like '#%'
UNION ALL
SELECT cast(cast( Replace(xy,',','.') as float) as datetime) xy
, [xy1]
, cast(xy2 as int) xy2
, [xy3]
, cast( Replace(xy4,',','.') as float) xy4
, [xy5]
, cast( Replace(xy6,',','.') as float) xy6
, cast( Replace([xy7],',','.') as float) [xy7]
, cast( Replace([xy8],',','.') as float) [xy8]
, cast( Replace([xy9],',','.') as float) [xy9]
, [xy10]
FROM 2020
WHERE xy7 not like '#%'

You can use UNION instead of UNION ALL if you don't want duplicates

CodePudding user response：

with cte
as
(
select * from 2019 WHERE xy7 not like '#%'
union all
select * from 2020 WHERE xy7 not like '#%'
union all
select * from 2021 WHERE xy7 not like '#%'
)
SELECT cast(cast( Replace(xy,',','.') as float) as datetime) xy
, [xy1]
, cast(xy2 as int) xy2
, [xy3]
, cast( Replace(xy4,',','.') as float) xy4
, [xy5]
, cast( Replace(xy6,',','.') as float) xy6
, cast( Replace([xy7],',','.') as float) [xy7]
, cast( Replace([xy8],',','.') as float) [xy8]
, cast( Replace([xy9],',','.') as float) [xy9]
, [xy10]
FROM cte