INFO: Modifying BCP Format File to Load into Specific Columns (52201)

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.

MORE INFORMATION

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