Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Superfast Table Copy (Revisited)

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
Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.