How do I perform the equivalent of a SQL Create Table as Select with Cypher

knowledge-base
ctas
create-table-as-sele

(Dana Canzano) #1

With a traditional SQL RDBMS one could perform a create table as select (i.e. CTAS) whereby its purpose is to create a
new table and copy existing data from the original table to the new copy. If you are looking to achieve the same with Cypher this can be performed via

MATCH (n:Movie) 
MERGE (m:New_Movie) 
SET m=n;

The above will copy all Movie nodes and their properties to a set of new nodes with the label New_Movie. If you
have a significant number of Movie nodes you may not want to copy all nodes in a single transaction, for example you may want to run
utilize apoc.periodic.commit

call apoc.periodic.commit("MATCH (n:Movie) with n limit {limit} create (m:New_Movie) set m=n",{limit:10000});

As with a CTAS statement, this does not copy any underlying schema indexes or constraints on said label. Additionally, if the Movie
nodes have additional labels they will not be copied to the New_Movie nodes.