HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

This is an old revision of the document!

---

====== COPY Statement ====== COPY statement allows to transfer data between tables and files. Use it to transfer relatively small volumes of data i.e. query results, look-up and dimension tables. When you copy data between tables they can be located in different databases. **Syntax**: Export data to a file: <code language=sql> COPY table_name | (select_stmt) TO [HDFS] file_name [options] options: DELIMITER 'char' | SQLINSERT target_table_name </code> Copy data between existing tables: <code language=sql> COPY table_name | (select_stmt) TO target_table_name AT target_conn_name [options] options: BATCHSIZE num </code> Notes: * //table_name// is an identifier. * //file_name// can be an expression, quoted or unquoted string. * If //file_name// already exists it is overwritten. * When HDFS is specified, the data is copied to a HDFS-compatible file system. * DELIMITER specifies the delimiter between column values, '\t' (TAB character) is the default. * SQLINSERT specifies to generate SQL INSERT statements to insert data to //target_table_name//. The statements are just stored in the file, and not executed. * When you copy data into another table, the table must exist. * BATCHSIZE specifies the number of rows to transfer in a single batch when copying rows between tables, the default is 1000. **Example 1**: Export the query results to a file: <code language=sql> COPY (SELECT id, name FROM sales.users WHERE local_dt = CURRENT_DATE) TO /data/users.txt DELIMITER '\t'; </code> **Example 2**: Generate SQL INSERT statements: <code language=sql> COPY sales.users TO /data/users2.sql SQLINSERT sales.users; </code> **Example 3**: Copy rows from between tables in different databases: <code language=sql> COPY sales.users TO sales.users2 AT tdconn; </code> **Compatibility:** HPL/SQL Extension, PostgreSQL **Version:** HPL/SQL 0.3.7 See also: * [[copy-from-local|COPY FROM LOCAL]]