Copy Command
Options
Copy Command Syntax
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
CopyOptions
Copy Options
• VALIDATION_MODE
• RETURN_FAILED_ONLY
• ON_ERROR
• FORCE
• SIZE_LIMIT
• TRUNCATECOLUMNS
• ENFORCE_LENGTH
• PURGE
• LOAD_UNCERTAIN_FILES
VALIDATION_MODE
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
VALIDATION_MODE = RETURN_n_ROWS|RETURN_ERRORS|RETURN_ALL_ERRORS;
Validate
the data files instead of loading them into the table
RETURN_ERRORS gives all errors in the files
RETURN_ALL_ERRORS gives all errors from previously loaded files if we have used
ON_ERROR = CONTINUE
RETURN_N_ROWS displays first N records and fails at the first error record
RETURN_FAILED_ONLY
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
RETURN_FAILED_ONLY = TRUE | FALSE ;
Specifies whether to return only files that have failed to load in the statement
result
Default is FALSE
ON_ERROR
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
ON_ERROR = CONTINUE | SKIP_FILE | SKIP_FILE_num |
SKIP_FILE_num% | ABORT_STATEMENT
CONTINUE – To skip error records and load remaining records
SKIP_FILE – To skip the files that contain errors
SKIP_FILE_Num – Skip a file when the number of error rows found in the file is equal to or
exceeds the specified number.
SKIP_FILE_Num% – Skip a file when the percentage of error rows found in the file exceeds the
specified percentage.
Default is ABORT_STATEMENT, Abort the load operation if any error is found in a data file.
FORCE
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
FORCE = TRUE | FALSE ;
To load all the files, regardless of whether they’ve been loaded previously
Default is False, if we don’t specify this property Copy command will not fail but
it skips loading the data
SIZE_LIMIT
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
SIZE_LIMIT = <NUMBER> ;
Specify maximum size in bytes of data loaded in that command
When the threshold is exceeded, the COPY operation stops loading
In case of multiple files of same pattern also it will stop after the size limit, that
means some files can be loaded fully and one file will be loaded partially
TRUNCATE_COLUMNS or ENFORCE_LENGTH
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
TRUNCATECOLUMNS = TRUE | FALSE – Default is False ;
(Or) ENFORCE_LENGTH = TRUE | FALSE – Default is TRUE ;
Specifies whether to truncate text strings that exceeds the target column length
Default is FALSE, that means if we don’t specify this option, and if text strings that
exceeds the target column length, then Copy command will fail
PURGE
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
PURGE = TRUE | FALSE
Specifies whether to remove the data files from the stage automatically after
the data is loaded successfully.
Default is FALSE
LOAD UNCERTAIN FILES
COPY INTO <table_name>
FROM @ExternalStage
FILES = ( '<file_name>','<file_name2>')
FILE_FORMAT = <file_format_name>
LOAD_UNCERTAIN_FILES = TRUE | FALSE
specifies to load files for which the load status is unknown. The COPY command
skips these files by default.
Note: The load status is unknown if all of the following conditions are true:
• The file’s LAST_MODIFIED date is older than 64 days.
• The initial set of data was loaded into the table more than 64 days earlier.
• If the file was already loaded successfully into the table, this event occurred more than 64 days earlier.
Thank You
