Is there anyway to write a copy command direct which will copy data from 1 table and populate another table (with some condition will be better)?
what I have observed copy command performance is far more better that INSERT INTO in vertica. So I am trying to replace the INSERT INTO with copy command.
Thanks!!
What you want to do is an
INSERT /*+ DIRECT */ INTO table2 SELECT ... FROM table1 WHERE .... The direct hint will make it do a direct load to ROS containers instead of through WOS. If you are doing large bulk loads, this would be fastest. If you are doing many small insert/selects like this, then it would be best to use WOS and leave out the DIRECT.Another possibility would be to do a
CREATE TABLE table2 AS SELECT ... FROM table1 WHERE ....Finally, if you are really just copying all the data and not filtering (which I know isn't your question, but I'm including this for completeness)... and the tables are partitioned, you can do a
COPY_PARTITONS_TO_TABLEwhich will just create references from the source table's ROS containeres to the target table. Any changes to the new table would reorganize the ROS containers (over time, using the tuple mover, etc. Containers wouldn't get cleaned up unless both tables reorganized them).