SQL Language Guide : 8. SQL Statements : COPY VWLOAD
 
Share this page                  
COPY VWLOAD
Valid in: SQL, ESQL, OpenAPI
The COPY...VWLOAD statement copies the contents of a file to a table using the VWLOAD operator. This operation is like using vwload -c on the command line, except that the load can be done from within a multi-statement transaction instead of in a separate session.
This statement has the following format:
COPY tablename() VWLOAD FROM 'datafile', ... [WITH option {,option}]
tablename()
Specifies the name of the table to be loaded. The column list is empty and not supported.
'datafile'
Specifies the names of the files that contain the data being loaded. File names are specified as a comma-separated list from one to many. An entry can be a file name, a file name with an asterisk as a wildcard, or a directory. If you specify a directory, all files in the directory are loaded. Valid use of wildcards in file names is as follows: *, abc*, *abc, abc*def, *abc*.
WITH option {,option}
Specifies optional WITH clause options:
INSERTMODE=
Specifies the mode to use for inserts and merges. Valid modes are:
ROW
Inserts through the PDT (allows concurrent inserts)
BULK
Appends directly to disk (does not allow concurrent inserts)
AUTO_DETECT_COMPRESSION
Detects GZ and ZIP files. Compressed files are automatically detected by the file contents, not by the file suffix. If you are loading from a directory or using a file name wildcard, you can mix regular text files, GZ, and ZIP.
ATTRIBUTES='col1, col2, col3,...'
Specifies list of columns to load. An empty col name indicates an input field that is to be ignored.
Default: All columns are loaded if no attributes argument is specified.
ROLLBACK=
Turns “roll back on error” on or off.
ON
(Default) Rolls back on error.
OFF
Does not roll back the load transaction when errors were encountered, causing partial data to be loaded.
CHARSET='charset'
Specifies the input character set (see vwload Supported Character Sets).
Default: no conversion
DATEFORMAT='col1=format1,col2=format2,...'
Sets date format for the column.
When no column is indicated, the format applies to all columns that are not otherwise set. That format must be defined last, without any assignment operation.
Valid values for format are described in vwload Date Format Settings.
Alternatively, a custom format string can be specified by starting the format with a '+' sign followed by the format string. The format string can consist of any of the format specifier characters used on the DATE_FORMAT function, described in the SQL Language Guide.
To be able to use any character in the format string (including for example, "," and "="), you must quote the format string in the same way that you would quote a delimited identifier on the command line (for example: 'ColX="+%M,%Y,%d",ColY=US'). For more information on delimited identifiers, see Regular and Delimited Identifiers.
Default: US
Examples:
WITH DATEFORMAT='col1=+'%d-%b-%y'
WITH DATEFORMAT='Col1=US,Col2=+%M %Y %d'
Define US date format for columns a2_us, a4_us and GERMAN for all other columns (a1_multi, a3_de, a5_de):
WITH ATTRIBUTES='a1_multi,a2_us,a3_de,a4_us,a5_de', FDELIM=',',
DATEFORMAT='a2_us=US,a4_us=US,GERMAN';
or, which accomplishes the same:
WITH ATTRIBUTES='a1_multi,a2_us,a3_de,a4_us,a5_de', FDELIM=',',
DATEFORMAT='a1_multi=GERMAN,a3_de=GERMAN,a5_de=GERMAN,US';
ERRCOUNT=n
Terminates after the first n errors in all input files. Default: 0 (do not terminate).
ESCAPE='escapechar'
Specifies the escape character to use. This allows escaping of single characters to allow a delimiter character to be part of a field (for example: \) or to allow a quote character to be part of a quoted string.
The argument must be a single ASCII character or an empty string. If the argument is an empty string, this functionality is disabled. To specify a control character, use an escape sequence (see vwload Escape Sequences).
Default: none
ESCAPES
Interprets data escape sequences
FDELIM='fielddelim'
Specifies field delimiter to use. The delimiter must be a single character. To specify a control character, use an escape sequence.
Default: |
Example: FDELIM=','
HEADER
Skips header line in files.
IGNFIRST
Ignores the first field.
IGNLAST
Ignores the last field.
LOG='dir'
Logs rejected rows and corresponding errors to the specified file. The file is created by the operation.
NULLVALUE='nullvalue'
Defines the string that identifies NULL values. Default: ' '
QUOTE='quotechar'
Specifies the quote character(s) to use. This allows the input to contain quoted fields (for example "Doe, John"), which may contain field or record delimiter characters. To include a quote character inside a quoted field, enter it twice in the input--for example: "The ""BIG"" Boss". When using distinct open and close quote characters, enter only the close quote character twice--for example: [The [BIG]] Boss].
The argument must be one or two ASCII characters or an empty string.
If the argument is an empty string, this functionality is disabled. If the argument is two characters, the first character is used as the opening quote character, and the second as the closing quote character. For example, specify "[]" to allow [quoted string].
To specify a control character, use an escape sequence.
Default: none
RDELIM='recorddelim'
Specifies record delimiter to use. The delimiter must be a single character. To specify a control character, use an escape sequence.
Default: \n
Example: RDELIM=','
STRICTNULLS
Uses strict NULL value checking. This distinguishes between plain and quoted or escaped occurrences of a NULL representation. For example, NULL is a NULL value, whereas "NULL" is a 4-character string value. By default, vwload does not make this distinction. This option allows proper loading of some data generated by MySQL and PostgreSQL.
SUBSTITUTE='substitutechar'
Substitutes the specified character for any invalid input character during character set conversion. When no substitute character is provided, an invalid input character is considered an error condition. Specifying a substitute character allows records that contain invalid characters to be loaded successfully.
The argument must be a single Unicode character or an empty string. If the argument is an empty string, the functionality is disabled.
To specify a control character or Unicode code point that cannot easily be typed on the keyboard, use an escape sequence.
TEXTMODE
Opens input files in text mode and does not perform newline conversion. On Linux, this may gain a 5 to 10 percent performance improvement. On Windows, there is no known advantage to using this option. (By default, vwload opens files in binary mode and performs automatic newline conversion, transparently handling text formats of various operating systems.)
Note:  When using -T on Windows, any ^Z (ASCII SUB) character in the input is interpreted as End-Of-File by the Windows library, and causes vwload not to see any data following the first such character.
WORK_DIR
Specifies a directory that will be used for relative paths in filenames. Default is /tmp, so specifying non-absolute paths without a WORK_DIR tells Vector to look for the files in /tmp.