4. SQL Statements : COPY : Filename Specification for COPY
 
Share this page                  
Filename Specification for COPY
Filename must be enclosed in single quotation marks; the file specification can include a directory/path name. For COPY INTO, if the file does not exist, COPY creates the file.
For COPY INTO, if the file already exists, COPY overwrites it.
Windows File Types for COPY
File type can be specified using the optional type parameter. Type must be either T for text, or B for binary.
The traditional Windows newline indicator is a CR-LF pair (carriage return / linefeed). The newline indicator on other operating systems (such as Linux) is a single linefeed with no carriage return. Windows uses the file type to control translation between Windows and Linux style newline indicators, as well as control-Z translation.
A file in binary type mode reads or writes the data exactly as is, with no translation. A file in text type mode translates a single LF to CR-LF when writing. When reading a file in text mode, CR-LF pairs are read as single LF's, and if a control-Z occurs in the data file, end-of-file is returned and Windows stops reading data from that file.
By default, Vector uses text mode for COPY INTO and COPY FROM only if all of the listed field formats are character types (c, char, text, varchar, or dummy). Otherwise, binary mode is used.
The binary-copy forms (COPY () FROM or COPY () INTO) use binary mode.
Note:  Unicode formats (nchar, nvarchar) cause binary mode to be used by default.
COPY FROM recognizes CR-LF as a newline (nl) delimiter even if the input file is read in binary type mode. (This is true on non-Windows systems too, so that data files that were created by Windows applications can be read.)
For situations where the default file type choice is inappropriate, the file type can be specified explicitly. For example, if COPY INTO is creating a file to be read on a Linux system, a file type of B (Binary) is appropriate. The resulting file will contain Linux-style newlines (single linefeeds) instead of Windows-style newlines.