COPY INTO

Databricks · Data Ingestion

Theory

COPY INTO is a SQL command that loads files from a volume or cloud storage path into a Delta table. It is idempotent: Databricks remembers which files the table has already loaded, so running the same command again only loads new files and skips the rest. You give the source path, the file format, optional FORMAT_OPTIONS (header, inferSchema, delimiter) and COPY_OPTIONS such as mergeSchema to allow new columns. You can also run a SELECT inside COPY INTO to cast or pick columns while loading.

Example: on Monday the folder has orders_01.csv and orders_02.csv and COPY INTO loads both. On Tuesday orders_03.csv arrives; running the same statement loads just that one file. Rerunning by mistake loads nothing, so there are no duplicates.

Why we use it

It gives SQL users a simple, retry-safe way to load batches of files without writing streaming code. Because it skips files it has already loaded, a job can rerun after a failure without creating duplicate rows.

When to use it

Use COPY INTO for scheduled batch loads of thousands of files, often from a SQL warehouse or a SQL task in a job. For millions of files, continuous arrival or complex schema evolution, prefer Auto Loader, which Databricks recommends for most incremental file ingestion.

How it works

  1. Run COPY INTO main.sales.orders_raw FROM '/Volumes/main/sales/raw/orders/'
  2. List files in the source path (optionally filtered by PATTERN)
  3. Was this file loaded into this table before? Yes: Skip the file. No: Parse it with FORMAT_OPTIONS.
  4. Apply the optional SELECT casts and mergeSchema rules
  5. Append rows and record the file as loaded
  6. Return num_inserted_rows

Syntax

COPY INTO <catalog.schema.table>
FROM '<path>' | (SELECT <cols> FROM '<path>')
FILEFORMAT = CSV | JSON | PARQUET | AVRO | ORC | TEXT | BINARYFILE
[FILES = ('a.csv', ...) | PATTERN = '<glob>']
[FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')]
[COPY_OPTIONS ('mergeSchema' = 'true', 'force' = 'false')]

Example

-- Empty placeholder table; schema comes from the files
CREATE TABLE IF NOT EXISTS main.sales.orders_raw;

COPY INTO main.sales.orders_raw
FROM '/Volumes/main/sales/raw/orders/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

-- Cast while loading
COPY INTO main.sales.orders
FROM (SELECT CAST(order_id AS BIGINT) AS order_id, customer_id, region,
             CAST(amount AS DECIMAL(10,2)) AS amount, CAST(order_date AS DATE) AS order_date
      FROM '/Volumes/main/sales/raw/orders/')
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true');

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic