Inserting Data from a Union Query into New Table-CodePudding

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).

CodePudding user response：

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 ...