---
title: "COPY statement"
description: "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."
canonical: "https://motherduck.com/glossary/copy-statement/"
related:
  - title: "COPY | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/copy/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Writing Data to Amazon S3 | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/cloud-storage/writing-to-s3/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# 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:

```sql
-- 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)`.

<glossary-callout guide="duckdb-cheatsheet-full" />

## 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:

```sql
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.