> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # COPY export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; The `COPY... FROM` statement copies data from `cockroach sql` or other third party clients 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 ### 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`, `INSERT`, `UPDATE`, `UPSERT`, or `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` to start a temporary, in-memory cluster with the `movr` database 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: Before you input the following rows, ensure the delimiters are tab characters. They may have been converted to spaces by the browser. ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 8a3d70a3-d70a-4000-8000-00000000001d seattle Hannah '400 Broad St' 0987654321 ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 9eb851eb-851e-4800-8000-00000000001e new york Carl '53 W 23rd St' 5678901234 ``` 4. Mark the end of data with `\.` on its own line: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} \. ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY 2 ``` 5. 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'); ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} "My name is Werner Brandes","My voice is my passport" ``` 4. Mark the end of data with `\.` on its own line: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} \. ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY 1 ``` 5. View the data in the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM setecastronomy; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} "My name is Werner Brandes","\"My\" \"voice\" \"is\" \"my\" \"passport\"" ``` 4. Mark the end of data with `\.` on its own line: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} \. ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY 1 ``` 5. View the data in the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM setecastronomy; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} "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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} \. ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY 1 ``` 5. View the data in the `setecastronomy` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM setecastronomy; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 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: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 1,X'6869 2,x'6869 3,"\x6869" 4,\x6869 ``` 5. Mark the end of data with `\.` on its own line: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} \. ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} COPY 4 ``` 6. View the data in the `mybytes` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT * FROM mybytes; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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` 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; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 * `IMPORT INTO` * `EXPORT` * Install a Driver or ORM Framework