Using SQL in DBeaver 22.2.3. I have data that is in different tables (with the same columns just different values). I used a Union statement to join all the data in my return. However, how can I move the data from that return into a new table (to combine all the data into one) so only certain columns are in it?
The code I tried was:
insert geom, ccn, report_dat, shift, "method", offense, ward, district, latitude, longitude, start_date, end_date
into normal
from c, c2, c3, c4, c5, c6;
What I expected was the data from all the tables would be in the table "normal", I had found the code on StackOverflow and applied it to my data (it did not work).
>Solution :
I suggest CREATE TABLE AS. And UNION ALL (unless you want to remove duplicates):
CREATE TABLE new_table AS
SELECT geom, ccn, report_dat, shift, method, offense, ward, district, latitude, longitude, start_date, end_date
FROM c
UNION ALL
SELECT geom, ccn, report_dat, shift, method, offense, ward, district, latitude, longitude, start_date, end_date
FROM c2
UNION ALL ...