Using a Data File with Fewer Fields

Administering SQL Server

Administering SQL Server

Using a Data File with Fewer Fields

In some cases, a data file may have fewer fields than there are columns in the table. For example, the New_auth.dat data file (ASCII, or character format) does not contain matching fields for the address and zip columns in the authors2 table.

The New_auth.dat file:

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

To bulk copy data selectively to the correct columns in authors2, create a default format file (Authors.fmt) with the following command:

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

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of authors2. The field terminator for every column should be a comma (,), except for the contract column, which should use the row terminator \n (newline) because it is the last column in the row. Also, the contract column has a file storage type of char, because the data file is an ASCII file. The address and zip columns should not have field terminators and should have their field length set to 0. When prompted for the format file name, specify Authors.fmt.

The Authors.fmt file:

8.0
9
1  SQLCHAR  0  11 ","      1  au_id       SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 ","      2  au_lname    SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  20 ","      3  au_fname    SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  12 ","      4  phone       SQL_Latin1_General_Cp437_BIN
5  SQLCHAR  0  0  ""       5  address     SQL_Latin1_General_Cp437_BIN
6  SQLCHAR  0  20 ","      6  city        SQL_Latin1_General_Cp437_BIN
7  SQLCHAR  0  2  ","      7  state       SQL_Latin1_General_Cp437_BIN
8  SQLCHAR  0  0  ""       8  zip         SQL_Latin1_General_Cp437_BIN
9  SQLCHAR  0  1  "\r\n"   9  contract    SQL_Latin1_General_Cp437_BIN

The format file contains all the information necessary to bulk copy data from the data file to the Microsoft® SQL Server™ table. A prefix length of 0, field length of 0, and no field terminator for address and zip means that these columns do not exist in the data file. However, the format file must be modified further with a text editor to ensure that no data will be loaded into address and zip. The server column numbers (sixth field in the format file) for these columns should be 0:

8.0
9
1  SQLCHAR  0  11 ","      1  au_id       SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 ","      2  au_lname    SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  20 ","      3  au_fname    SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  12 ","      4  phone       SQL_Latin1_General_Cp437_BIN
5  SQLCHAR  0  0  ""       0  address     SQL_Latin1_General_Cp437_BIN
6  SQLCHAR  0  20 ","      6  city        SQL_Latin1_General_Cp437_BIN
7  SQLCHAR  0  2  ","      7  state       SQL_Latin1_General_Cp437_BIN
8  SQLCHAR  0  0  ""       0  zip         SQL_Latin1_General_Cp437_BIN
9  SQLCHAR  0  1  "\r\n"   9  contract    SQL_Latin1_General_Cp437_BIN

The data in the data file can now be bulk copied into authors2 using the command:

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

Alternatively, you can use the BULK INSERT statement from a query tool, such as SQL Query Analyzer, to bulk copy data:

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

Note  Because address and zip are not present in the data file, those columns will contain NULL in the SQL Server table if no DEFAULT values have been defined. Therefore, authors2 must allow null values in those columns.