ID Number: Q52201
1.10 1.11 4.20
OS/2
Summary:
The BCP format file can be modified to skip over data in the BCP
input data file and be loaded into specific columns of a table. The
fifth column of the BCP format file identifies which column (first,
second, third, etc.) of a table the corresponding field of the input
data file is loaded into. The values of the column identifiers can be
changed to redirect the load of the input data file.
For example, when a 0 (zero) is placed in the fifth column value in
the .FMT file, BCP does not insert that data field (defined by the
number in the first column in the .FMT file) into the table. This
fact can be used to load either a data file with fewer or more data
fields than the number of columns in a table. If more columns are in
the database table than are in the input data file, the columns not
being loaded with data must be defined to accept null values.
Listed below are "before" and "after" examples of a BCP format file
that was modified to load the fifth, sixth, and seventh field
positions of a 10-field input file into the first, second, and
third columns of a table.
Before
------
All input file fields are loaded into all table columns.
4.0
10
1 SYBCHAR1 512 "" 1 C1
2 SYBCHAR1 512 "" 2 C2
3 SYBCHAR1 512 "" 3 C3
4 SYBCHAR1 512 "" 4 C4
5 SYBCHAR1 512 "" 5 C5
6 SYBCHAR1 512 "" 6 C6
7 SYBCHAR1 512 "" 7 C7
8 SYBCHAR1 512 "" 8 C8
9 SYBCHAR1 512 "" 9 C9
10 SYBCHAR1 512 "" 10 C10
After
-----
The fifth, sixth, and seventh field positions of the input file are
loaded in the first, second, and third columns of a table. Please note
that nonzeroed columns of the table are not required to be in any
specific order. Other possible combinations are: 132, 231, 213, 312,
and 321.
This "after" format file can be used to load a table with 10 columns
with the columns not receiving data defined to allow nulls, or used to
load a table with three columns.
4.0
10
1 SYBCHAR1 512 "" 0 C1
2 SYBCHAR1 512 "" 0 C2
3 SYBCHAR1 512 "" 0 C3
4 SYBCHAR1 512 "" 0 C4
5 SYBCHAR1 512 "" 1 C5
6 SYBCHAR1 512 "" 2 C6
7 SYBCHAR1 512 "" 3 C7
8 SYBCHAR1 512 "" 0 C8
9 SYBCHAR1 512 "" 0 C9
10 SYBCHAR1 512 "" 0 C10
Additional reference words: BCP