COPY statement
The COPY statement bulk-transfers data between a table and an external file, exporting query results to formats like CSV or Parquet, or loading files directly into a table.
Overview
COPY is the standard bulk import/export mechanism in SQL engines that support it, handling large file transfers more efficiently than row-by-row INSERT statements. It has two directions:
Copy code
-- Export a table (or query result) to a file
COPY orders TO 'orders.csv' (FORMAT csv, HEADER true);
-- Load a file into an existing table
COPY orders FROM 'orders.csv' (FORMAT csv, HEADER true);
You can also export the result of an arbitrary query rather than a whole table by wrapping it in parentheses: COPY (SELECT * FROM orders WHERE status = 'shipped') TO 'shipped_orders.parquet' (FORMAT parquet).
DuckDB specifics
DuckDB's COPY supports CSV, Parquet, and JSON as both source and destination formats, with format-specific options (DELIMITER, HEADER, COMPRESSION, etc.) passed in parentheses. A DuckDB-specific capability is the PARTITION_BY option on COPY ... TO, which writes a Hive-partitioned directory structure directly from a query — useful for producing a data lake layout that other engines can prune on read:
Copy code
COPY (SELECT * FROM orders)
TO 's3://my-bucket/orders'
(FORMAT parquet, PARTITION_BY (order_year, order_month), OVERWRITE_OR_IGNORE true);
This produces paths like orders/order_year=2026/order_month=7/data_0.parquet. Because DuckDB reads directly from HTTP(S), S3, and other object storage via httpfs, COPY ... FROM and COPY ... TO work equally well against local files and remote paths, making COPY a common entry/exit point for DuckDB-based ELT pipelines.
Related terms
DuckDB can read CSV, Parquet, JSON, and other file formats directly with SQL table functions, whether the files are local, remote over HTTP/S3, or matched in bulk with a glob pattern.
Parquet →Apache Parquet is a columnar storage file format designed for efficient data processing and analytics.
Parquet vs CSV →Parquet is a compressed, columnar binary file format optimized for analytics, while CSV is a plain-text, row-based format with no built-in schema or compression — Parquet is generally faster and smaller for analytical workloads, while CSV is simpler and universally readable.
CREATE TABLE AS SELECT (CTAS) →CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.
INSERT statement →The INSERT statement is a fundamental SQL command used to add new rows of data into a table.
Table format →A table format is a metadata layer on top of data files in a lake or object storage that defines what constitutes a table — its schema, partitioning, and file list — enabling ACID transactions, schema evolution, and time travel across multiple query engines.
FAQS
Wrap a query in parentheses as the COPY source, e.g. COPY (SELECT * FROM orders WHERE status = 'shipped') TO 'shipped.parquet' (FORMAT parquet), instead of naming a table directly.
Yes, using the PARTITION_BY option on COPY ... TO, which writes a directory structure partitioned by one or more columns (e.g., partitioned by year and month), similar to how partitioned datasets are laid out in a data lake.
