Step 5. Copy Data into the Target Tables

Execute COPY INTO <table> to load your staged data into the target table.

The following examples include the ON_ERROR = 'skip_file' parameter value. If the command encounters a data error on any of the records, it skips the file. If you do not specify an ON_ERROR value, the default is ON_ERROR = 'abort_statement', which aborts the COPY command on the first error encountered on any of the records in a file.

Note

Loading data into tables requires a warehouse. If you created a warehouse by following the instructions in the prerequisites, skip to the next section. If you are using a warehouse that is not configured to auto resume, execute ALTER WAREHOUSE to resume the warehouse. Note that starting the warehouse could take up to five minutes.

ALTER WAREHOUSE mywarehouse RESUME;

CSV

The following example loads data from the file named contacts1.csv.gz into the mycsvtable table.

COPY INTO mycsvtable
  FROM @my_csv_stage/contacts1.csv.gz
  FILE_FORMAT = (FORMAT_NAME = mycsvformat)
  ON_ERROR = 'skip_file';

Snowflake returns the following results indicating he data in contacts1.csv.gz was loaded successfully.

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts1.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

The following example uses pattern matching to load data from all files that match the regular expression .*contacts[1-5].csv.gz into the mycsvtable table.

COPY INTO mycsvtable
  FROM @my_csv_stage
  FILE_FORMAT = (FORMAT_NAME = mycsvformat)
  PATTERN='.*contacts[1-5].csv.gz'
  ON_ERROR = 'skip_file';

Snowflake returns the following results:

  • The data in contacts1.csv.gz is ignored because you already loaded the data successfully.

  • The data in the following files was loaded successfully:

    • contacts2.csv.gz

    • contacts4.csv.gz

    • contacts5.csv.gz

  • The data in contacts3.csv.gz was skipped due to 2 data errors. The next step in this tutorial addresses how to validate and fix the errors.

+-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
| file                        | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts2.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
| mycsvtable/contacts3.csv.gz | LOAD_FAILED |           5 |           0 |           1 |           2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error |                3 |                     1 | "MYCSVTABLE"[11]        |
| mycsvtable/contacts4.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
| mycsvtable/contacts5.csv.gz | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
+-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+

JSON

The following example loads data from the file named contacts.json.gz into the myjsontable table.

COPY INTO myjsontable
  FROM @my_json_stage/contacts.json.gz
  FILE_FORMAT = (FORMAT_NAME = myjsonformat)
  ON_ERROR = 'skip_file';

Snowflake returns the following results indicating the data in contacts1.csv.gz was loaded successfully.

+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                         | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| myjsontable/contacts.json.gz | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Next: Step 6. Resolve Data Load Errors Related to Data Issues