Use a data file with fewer Fields

Use a data file with fewer Fields

In some cases, the number of fields contained in the data file may be less than the number of columns in the table. For example, the new_auth.dat data file (ASCII format, that is, the character format) does not containAuthors2TableAddressAndZipColumns.

The content of the new_auth.dat file is as follows:

777-77-7777,Smith,Chris,303 555-1213,Denver,CO,1888-88-8888,Doe,John,206 555-1214,Seattle,WA,0999-99-9999,Door,Jane,406 555-1234,Bozeman,MT,1

To selectively copy large volumes of dataAuthors2Use the following command to create the default format file (authors. FMT ):

bcp pubs..authors2 out c:\authors.txt -Sservername -Usa -Ppassword

BCPThe utility will prompt you to enterAuthors2The file storage type, prefix length, field length, and field terminator of each column. The field terminator of each column must be a comma (,),ContractExcept for columns, this column should use the line terminator \ n (line break) because this column is the last column in the row. In addition, because the data file is an ASCII fileContractThe file storage type of the column isChar.AddressAndZipThe column should not have a field Terminator, and its field length should be set to 0. Specify authors. FMT when prompted to enter the format file name.

The content of the authors. FMT file is as follows:

8.091  SQLCHAR  0  11 ","      1  au_id       SQL_Latin1_General_Cp437_BIN2  SQLCHAR  0  40 ","      2  au_lname    SQL_Latin1_General_Cp437_BIN3  SQLCHAR  0  20 ","      3  au_fname    SQL_Latin1_General_Cp437_BIN4  SQLCHAR  0  12 ","      4  phone       SQL_Latin1_General_Cp437_BIN5  SQLCHAR  0  0  ""       5  address     SQL_Latin1_General_Cp437_BIN6  SQLCHAR  0  20 ","      6  city        SQL_Latin1_General_Cp437_BIN7  SQLCHAR  0  2  ","      7  state       SQL_Latin1_General_Cp437_BIN8  SQLCHAR  0  0  ""       8  zip         SQL_Latin1_General_Cp437_BIN9  SQLCHAR  0  1  "\r\n"   9  contract    SQL_Latin1_General_Cp437_BIN

This format file contains all the information required to copy data from a large data file to a Microsoft SQL Server table.AddressAndZipThe prefix length is 0, the field length is 0, and there is no field Terminator. This indicates that the two columns do not exist in the data file. However, you must use a text editor to further modify the format file to ensure that no data is loadedAddressAndZip. The server column numbers of these two columns (the sixth field in the format file) should be 0:

8.091  SQLCHAR  0  11 ","      1  au_id       SQL_Latin1_General_Cp437_BIN2  SQLCHAR  0  40 ","      2  au_lname    SQL_Latin1_General_Cp437_BIN3  SQLCHAR  0  20 ","      3  au_fname    SQL_Latin1_General_Cp437_BIN4  SQLCHAR  0  12 ","      4  phone       SQL_Latin1_General_Cp437_BIN5  SQLCHAR  0  0  ""       0  address     SQL_Latin1_General_Cp437_BIN6  SQLCHAR  0  20 ","      6  city        SQL_Latin1_General_Cp437_BIN7  SQLCHAR  0  2  ","      7  state       SQL_Latin1_General_Cp437_BIN8  SQLCHAR  0  0  ""       0  zip         SQL_Latin1_General_Cp437_BIN9  SQLCHAR  0  1  "\r\n"   9  contract    SQL_Latin1_General_Cp437_BIN

To copy the data in this data file to a large capacityAuthors2, You can use the following command:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword

In addition, you can use the bulk insert statement in a query tool such as SQL query analyzer to copy data in large capacity:

BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat' WITH (FORMATFILE = 'c:\authors.fmt')

DescriptionBecause the data file does not containAddressAndZipColumn, so if the default value is not defined, the two columns in the SQL Server table will contain null. Therefore,Authors2The two columns must contain null values.