Using a Data File with More Fields
In some cases, a data file may have more fields than there are columns in the table. For example, the New_auth.dat data file (ASCII, or character format) contains two fields (age and salutation) not contained on authors2. These fields will be omitted, or skipped, during the bulk copy procedure.
The New_auth.dat file:
777-77-7777,Smith,Chris,303 555-1213,27 College Ave,Denver,CO,80220,1,28,Ms.
888-88-8888,Doe,John,206 555-1214,123 Maple Street,Seattle,WA,95099,0,35,Mr.
999-99-9999,Door,Jane,406 555-1234,45 East Main,Bozeman,MT,59715,1,33,Mrs.
To bulk copy data selectively to the correct columns in authors2 only, create a default format file (Authors.fmt) with the 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 (,). Also, the contract column has a file storage type of char because the data file is an ASCII file. 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 40 "," 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 5 "," 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. However, the format file needs to be modified further with a text editor to reflect the addition of two new columns: age and salutation. The second line of the format file specifies the number of columns and should now be changed to 11 because there are 11 fields in the data file. Two new rows need to be added to the end of the format file to provide format information for the additional fields. The row terminator needs to be moved from the contract column to the salutation column and the server column numbers (sixth field in the format file) for the age and salutation columns should be 0:
8.0
11
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 40 "," 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 5 "," 8 zip SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 0 1 "," 9 contract SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 0 0 "," 0 age SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 0 0 "\r\n" 0 salutation 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')