I am attempting to take data from 2 tables, find intersecting geometries, and insert rows into the new table representing the intersections. I have done this in the past with CartoDB using SQL but now I'm not able to do so. I'd also be open to using the Merge Dataset functionality via the UI, but that doesn't appear to be designed for what I need.
Specifically, when I run the following sql via the UI, I get an error stating "SELECT ... INTO is not allowed here".
SELECT clipped.city_name, the_geom INTO newtable FROM (SELECT table1.city_name, table2.namelsad, (ST_Dump(ST_Intersection(table2.the_geom, table1.the_geom))).geom As the_geom FROM table2 INNER JOIN table1 ON ST_Intersects(us_county.the_geom, us_or_city.the_geom)) As clipped
Any suggestions on how to make this work?