Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use COPY INTO in Snowflake: Syntax and Examples

Use the COPY INTO command in Snowflake to load data from external files or stages into a table. Specify the target table, source location, and file format options to control how data is imported.
📐

Syntax

The COPY INTO command loads data from a file or stage into a Snowflake table. You specify the target table, the source location (like a stage or external storage), and options such as file format and error handling.

  • target_table: The table where data will be loaded.
  • source_location: The file path or stage containing the data files.
  • FILE_FORMAT: Defines how to interpret the file (CSV, JSON, etc.).
  • ON_ERROR: Defines what to do if errors occur (skip, abort, continue).
sql
COPY INTO target_table
FROM @stage_name/path/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1)
ON_ERROR = 'CONTINUE';
💻

Example

This example loads CSV data from a named internal stage into a Snowflake table called customers. It skips the header row and continues loading even if some rows have errors.

sql
COPY INTO customers
FROM @my_csv_stage/customer_data/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1)
ON_ERROR = 'CONTINUE';
Output
COPY executed successfully. Loaded 1000 rows with 2 errors skipped.
⚠️

Common Pitfalls

Common mistakes when using COPY INTO include:

  • Not specifying the correct FILE_FORMAT, causing parsing errors.
  • Forgetting to skip header rows in CSV files, which can cause load failures.
  • Using incorrect stage or file paths, resulting in no data loaded.
  • Ignoring error handling, which can stop the entire load on the first error.

Always verify your file format matches the data and use ON_ERROR to control error behavior.

sql
/* Wrong: Missing SKIP_HEADER causes header to load as data */
COPY INTO customers
FROM @my_csv_stage/customer_data/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',');

/* Right: Skip header row to avoid errors */
COPY INTO customers
FROM @my_csv_stage/customer_data/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);
📊

Quick Reference

OptionDescriptionExample
target_tableTable to load data intocustomers
source_locationStage or file path with data@my_csv_stage/customer_data/
FILE_FORMATFormat of input filesTYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1
ON_ERRORError handling strategy'CONTINUE', 'SKIP_FILE', 'ABORT_STATEMENT'
PURGEDelete files after loadTRUE or FALSE
✅

Key Takeaways

Use COPY INTO to load data files into Snowflake tables efficiently.
Always specify the correct FILE_FORMAT to match your data structure.
Use ON_ERROR to control how errors during loading are handled.
Remember to skip header rows in CSV files to avoid loading errors.
Verify your stage and file paths are correct before running COPY INTO.