Copy Table – more powerful
syntax
COPY FROM <schema>/<password>@<database> TO <schema>/<password>@<database> CREATE | REPLACE | INSERT | APPEND <tablename> USING <Select clause that returns a results set>
example
COPY FROM one-schema/one-schemas-password@one-database TO another-schema/another-schemas-password@another-database> CREATE replica-table USING SELECT * FROM original-table
Good idea to create a script as below.
SET COPYCOMMIT 5000;
COPY FROM <schema>/<password>@<database> TO <schema>/<password>@<database> CREATE | REPLACE | INSERT | APPEND <tablename> USING <Select clause that returns a results set>;
EXIT
| CREATE REPLACE INSERT APPEND |
– If the destination table already exists, copy will report an error, otherwise the table is created and the data is copied. – If the destination table exists, copy will drop and recreate the table with the newly copied data. Otherwise, it will create the table and populate it with the data. – If the destination table exists, copy inserts the new rows into the table. Otherwise, copy reports an error and aborts. – Inserts the data into the table if it exists, otherwise it will create the table and then insert the data. |