Step 4. Copy Data into the Target Table¶
Execute COPY INTO <table> to load your staged data into the target table.
The COPY INTO <table> command uses the virtual warehouse you created in Step 2. Create Snowflake Objects to copy files.
COPY INTO emp_basic FROM @%emp_basic FILE_FORMAT = (type = csv field_optionally_enclosed_by='"') PATTERN = '.*employees0[1-5].csv.gz' ON_ERROR = 'skip_file';
Where:
The
FROMclause specifies the location containing the data files (the internal stage for the table).The
FILE_FORMATclause specifies the file type as CSV, and specifies the double-quote character (") as the character used to enclose strings. Snowflake supports diverse file types and options. These are described in CREATE FILE FORMAT.The
PATTERNclause specifies that the command should load data from the filenames matching this regular expression (.*employees0[1-5].csv.gz).The
ON_ERRORclause specifies what to do when the COPY command encounters errors in the files. By default, the command stops loading data when the first error is encountered; however, we’ve instructed it to skip any file containing an error and move on to loading the next file. Note that this is just for illustration purposes; none of the files in this tutorial contain errors.
The COPY command also provides an option for validating files before they are loaded. See the COPY INTO <table> topic and the other data loading tutorials for additional error checking and validation instructions.
The COPY command returns a result showing the list of files copied and related information:
+--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | employees02.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees04.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees05.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees03.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | employees01.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | +--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+