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
>Solution :
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