Superfast Table Copy (Revisited): Quick Tip

Jim Knicely authored this tip. You learned from a Vertica Quick Tip back on 02/07/2018 that Vertica has the lightweight, in-memory COPY_TABLE function. This awesome function lets us copy huge tables in less than a second. I wanted to revisit this awesome feature to point out that when using the COPY_TABLE command, if the target table already exists, Vertica appends the source to the existing table. Effectively, it gives you a superfast data insert! Example: dbadmin=> SELECT COUNT(*) FROM big_varchar_table; COUNT ------------ 1000000000 (1 row) dbadmin=> \timing on Timing is on. dbadmin=> SELECT COPY_TABLE(‘big_varchar_table’, ‘big_varchar_table2’); COPY_TABLE ———————————————————————— Created table big_varchar_table2. Copied table big_varchar_table to big_varchar_table2 (1 row) Time: First fetch (1 row): 184.013 ms. All rows formatted: 184.095 ms dbadmin=> SELECT COUNT(*) FROM big_varchar_table2; COUNT ———— 1000000000 (1 row) Time: First fetch (1 row): 761.344 ms. All rows formatted: 761.382 ms dbadmin=> SELECT COPY_TABLE(‘big_varchar_table’, ‘big_varchar_table2’); COPY_TABLE ——————————————————- Copied table big_varchar_table to big_varchar_table2 (1 row) Time: First fetch (1 row): 21.029 ms. All rows formatted: 21.073 ms dbadmin=> SELECT COUNT(*) FROM big_varchar_table2; COUNT ———— 2000000000 (1 row) Time: First fetch (1 row): 765.694 ms. All rows formatted: 765.758 ms Have Fun!