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
| Option | Description | Example |
|---|---|---|
| target_table | Table to load data into | customers |
| source_location | Stage or file path with data | @my_csv_stage/customer_data/ |
| FILE_FORMAT | Format of input files | TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1 |
| ON_ERROR | Error handling strategy | 'CONTINUE', 'SKIP_FILE', 'ABORT_STATEMENT' |
| PURGE | Delete files after load | TRUE 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.
