> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # COPY The `COPY ... FROM` statement copies data from [`cockroach sql`](/docs/stable/cockroach-sql) or other [third party clients](/docs/stable/install-client-drivers.md) to tables in your cluster. The `COPY ... TO` statement allows you to export a table or arbitrary query in a text or CSV format. ## Syntax ![copy syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/copy.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=aafd0eafd54f94320024c60e0ed35b0c) ### Parameters | Parameter | Description | | - | - | | `table_name` | The name of the table to which to copy data. | | `opt_column_list` | The column name, or list of column names, to which to copy data. | | `WITH copy_options` | Optionally specify one or more [copy options](#options). | | `query` | A [`SELECT`](/docs/stable/select-clause), [`INSERT`](/docs/stable/insert), [`UPDATE`](/docs/stable/update), [`UPSERT`](/docs/stable/upsert), or [`DELETE`](/docs/stable/delete) statement for which to copy results. | ### Options | Option | Description | | - | - | | `DELIMITER 'value'` | The value that delimits the rows of input data, passed as a string. | | `NULL 'value'` | The string that represents a `NULL` value in the input data. | | `BINARY` | Copy data `FROM` binary format. If `BINARY` is specified, no other format can be specified. If no format is specified, CockroachDB copies in plaintext format. | | `CSV` | Copy data `FROM` CSV format, or copy data `TO` CSV format. If `CSV` is specified, no other format can be specified. If no format is specified, CockroachDB copies in plaintext format. | | `ESCAPE` | Specify an escape character for quoting the fields in CSV data. | | `HEADER` | Specify that CockroachDB should skip the header in CSV data (first line of input). | ## Required privileges Only members of the `admin` role can run `COPY` statements. By default, the `root` user belongs to the `admin` role. ## Unsupported syntax CockroachDB does not yet support the following `COPY` syntax: * `COPY ... WITH FREEZE`. * `COPY ... WITH QUOTE`. * `COPY ... FROM ... WHERE `. ## Examples To run the examples, use [`cockroach demo`](/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr` database](/docs/stable/movr) preloaded. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach demo ``` ### Copy tab-delimited data to CockroachDB 1. Start copying data to the `users` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY users FROM STDIN; ``` 2. You will see the following prompt: ``` Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. ``` 3. Enter some tab-delimited data to copy to the table: > [!CAUTION] > > Before you input the following rows, ensure the delimiters are tab characters. They may have been converted to spaces by the browser. ``` 8a3d70a3-d70a-4000-8000-00000000001d seattle Hannah '400 Broad St' 0987654321 ``` ``` 9eb851eb-851e-4800-8000-00000000001e new york Carl '53 W 23rd St' 5678901234 ``` 1. Mark the end of data with `\.` on its own line: ``` \. ``` ``` COPY 2 ``` 2. Query the `users` table for the rows that you just inserted: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM users WHERE id IN ('8a3d70a3-d70a-4000-8000-00000000001d', '9eb851eb-851e-4800-8000-00000000001e'); ``` ``` id | city | name | address | credit_card --------------------------------------+----------+--------+----------------+------------- 9eb851eb-851e-4800-8000-00000000001e | new york | Carl | '53 W 23rd St' | 5678901234 8a3d70a3-d70a-4000-8000-00000000001d | seattle | Hannah | '400 Broad St' | 0987654321 (2 rows) ``` ### Copy CSV-delimited data to CockroachDB You can copy CSV data into CockroachDB using the following methods: * [Copy CSV-delimited data from `stdin`](#copy-csv-delimited-data-from-stdin) * [Copy CSV-delimited data from `stdin` with an escape character](#copy-csv-delimited-data-from-stdin-with-an-escape-character) * [Copy CSV-delimited data from `stdin` with a header](#copy-csv-delimited-data-from-stdin-with-a-header) * [Copy CSV-delimited data from `stdin` with hex-encoded byte array data](#copy-csv-delimited-data-from-stdin-with-hex-encoded-byte-array-data) #### Copy CSV-delimited data from `stdin` 1. Create a new table that you will load with CSV-formatted data: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING); ``` 2. Start copying data to the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY setecastronomy FROM STDIN WITH CSV; ``` You will see the following prompt: ``` Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. ``` 3. Enter some CSV-delimited data to copy to the table: ``` "My name is Werner Brandes","My voice is my passport" ``` 4. Mark the end of data with `\.` on its own line: ``` \. ``` ``` COPY 1 ``` 5. View the data in the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM setecastronomy; ``` ``` name | phrase ----------------------------+------------------------------------ My name is Werner Brandes | My voice is my passport (1 row) ``` #### Copy CSV-delimited data from `stdin` with an escape character 1. Create a new table that you will load with CSV-formatted data: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING); ``` 2. Start copying data to the `setecastronomy` table, specifying an escape character for quoting the fields: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY setecastronomy FROM STDIN WITH CSV DELIMITER ',' ESCAPE '\'; ``` You will see the following prompt: ``` Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. ``` 3. Enter some CSV-delimited data to copy to the table: ``` "My name is Werner Brandes","\"My\" \"voice\" \"is\" \"my\" \"passport\"" ``` 4. Mark the end of data with `\.` on its own line: ``` \. ``` ``` COPY 1 ``` 5. View the data in the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM setecastronomy; ``` ``` name | phrase ----------------------------+------------------------------------ My name is Werner Brandes | My voice is my passport My name is Werner Brandes | "My" "voice" "is" "my" "passport" (2 rows) ``` #### Copy CSV-delimited data from `stdin` with a header 1. Create a new table that you will load with CSV-formatted data: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING); ``` 2. Start copying data to the `setecastronomy` table, specifying that CockroachDB should skip the header (first line of CSV input): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY setecastronomy FROM STDIN WITH CSV HEADER; ``` 3. Enter the data, including the header line: ``` "name","phrase" "Hi, my name is Werner Brandes","My voice is my passport; verify me" ``` 4. Mark the end of data with `\.` on its own line: ``` \. ``` ``` COPY 1 ``` 5. View the data in the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM setecastronomy; ``` ``` name | phrase --------------------------------+------------------------------------- My name is Werner Brandes | My voice is my passport My name is Werner Brandes | "My" "voice" "is" "my" "passport" Hi, my name is Werner Brandes | My voice is my passport; verify me (3 rows) ``` #### Copy CSV-delimited data from `stdin` with hex-encoded byte array data 1. Create a new table that you will load with CSV-formatted data: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE IF NOT EXISTS mybytes(a INT PRIMARY KEY, b BYTEA); ``` 2. Set the `bytea_output` [session variable](/docs/stable/set-vars.md#supported-variables) to specify that CockroachDB should ingest hex-encoded byte array data: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET bytea_output = 'escape'; ``` 3. Start copying data to the `mybytes` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY mybytes FROM STDIN WITH CSV; ``` 4. Enter some CSV-delimited data to copy to the table: ``` 1,X'6869 2,x'6869 3,"\x6869" 4,\x6869 ``` 5. Mark the end of data with `\.` on its own line: ``` \. ``` ``` COPY 4 ``` 6. View the data in the `mybytes` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM mybytes; ``` ``` a | b ----+--------- 1 | X'6869 2 | x'6869 3 | hi 4 | hi (4 rows) ``` ### Copy data to `stdout` in CSV format 1. Start a temporary, in-memory cluster with the [`movr`](/docs/stable/movr) sample dataset preloaded: 2. Copy five rows from the `users` table to `stdout`, specifying the `CSV` [option](#options): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY (SELECT * FROM users LIMIT 5) TO STDOUT WITH CSV; ``` ``` ae147ae1-47ae-4800-8000-000000000022,amsterdam,Christine Crosby,10563 Mcfarland Burg Apt. 34,1007823073 b3333333-3333-4000-8000-000000000023,amsterdam,Natalie Barnes,58875 Monique Port,4777504042 b851eb85-1eb8-4000-8000-000000000024,amsterdam,Brenda Meyer,82208 Jamie Track Suite 57,3209048436 bd70a3d7-0a3d-4000-8000-000000000025,amsterdam,Jose Nelson,31068 Mark Mall,2715842506 c28f5c28-f5c2-4000-8000-000000000026,amsterdam,Anna Bennett,80284 Jeffery Courts,1695553015 ``` ## See also * [Migration Overview](/docs/molt/migration-overview.md) * [`IMPORT INTO`](/docs/stable/import-into) * [`EXPORT`](/docs/stable/export) * [Install a Driver or ORM Framework](/docs/stable/install-client-drivers.md)