3. Statements : OpenROAD SQL Statements : Copy Statement : Formatted Copying
 
Share this page                  
Formatted Copying
Formatted copying allows the type, number, and order of columns in the data file to differ from the table. By specifying a list of columns and their types in the copy statement, you instruct Ingres to perform a formatted copy. The copy statement list specifies the order and type of columns in the data file. Ingres uses the column names in the list to match up file data with the corresponding columns in the table.
For human readable text data files, the copy list formats will almost always be a character type: char, c, text, or less commonly varchar or byte. The copy statement converts (character) file data into table data types for copy from, or the reverse for copy into. The copy list may contain other types as well, such as integer or decimal, but these are binary types for special programming situations; they are not human readable types. Copy also supports a "dummy" type, used to skip input data (from) or insert fixed output text (into).
If some table columns are not listed in the copy list for a copy from, those columns are defaulted. (If they are defined in the table as not default, an error occurs.) If some table columns are not listed for a copy into, those table columns simply do not appear in the output data file.
The order of columns in the table need not match the order in the data file. Remember that the order of columns in the copy list reflects the order in the data file, not the order in the table. Additionally, a table column may be named more than once. (For copy from, if a column is named multiple times, the last occurrence in the copy list is the one that is stored into the table. Earlier occurrences undergo format conversion, but the result is discarded.)
Special restriction: If the table includes one or more long columns (such as long varchar or long byte), columns cannot be reordered across any long column. For example, if the table contains (int a, int b, long varchar c), a copy statement could use the order (b,a,c); but a copy statement asking for (a,c,b) would be illegal (you cannot move column b to occur after the long column c).
The values in the data file can be fixed-length, or variable-length. Values can optionally be ended with a delimiter (see Delimiters in the Data File); the delimiter is specified in the copy list. copy can also process a special case of delimited values, the comma separated values (CSV) delimiting form.
Note:  If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a fixed-length copy type) by a space. For example:
copy table t (col1=c20, col2=c30, d0=nl) into ‘t.out’: