Superfast Table Copy (Revisited)

[Deleted User]

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