[GIS] How to add rows from a Dataset to another

Question

The DB I have is divided in many datasets (shape format, each .zip I send to CartodDB consist of 4 files, .dbf, .prj, .shp, .shx). The problem is that the free account only allows 4 datasets, each one associated to a layer.

To solve this problem I'd like to join all my datasets in just one. All datasets share the same structure (same columns) due to the fact they form part of the same DB. I hope there is a way to add rows from one dataset to another.

Answer 1

The union all approach will just join the tables in as a "query view". You can use the option "create dataset from query" in order to store all the results in the same dataset.

Another approach would be using INSERTS in order to append your tables (aka: "insert rows from one dataset to another", for example:

-- Insert data from table2 to table1

INSERT INTO table_1 (column1, column2, column3, column4)
    SELECT column1, column2, column3, column4
    FROM table_2

-- Insert data from table3 to table1

INSERT INTO table_1 (column1, column2, column3, column4)
    SELECT column1, column2, column3, column4
    FROM table_3

-- Insert data from table4 to table1

INSERT INTO table_1 (column1, column2, column3, column4)
    SELECT column1, column2, column3, column4
    FROM table_4