In this example, the New_auth.dat data file (ASCII, or character format) contains two fields (age and salutation) that the authors2 table does not contain. 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:
7.0
9
1 |
SQLCHAR |
0 |
11 |
"," |
1 |
au_id |
2 |
SQLCHAR |
0 |
40 |
"," |
2 |
au_lname |
3 |
SQLCHAR |
0 |
20 |
"," |
3 |
au_fname |
4 |
SQLCHAR |
0 |
12 |
"," |
4 |
phone |
5 |
SQLCHAR |
0 |
40 |
"," |
5 |
address |
6 |
SQLCHAR |
0 |
20 |
"," |
6 |
city |
7 |
SQLCHAR |
0 |
2 |
"," |
7 |
state |
8 |
SQLCHAR |
0 |
5 |
"," |
8 |
zip |
9 |
SQLCHAR |
0 |
1 |
"\r\n" |
9 |
contract |
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:
7.0
11
1 |
SQLCHAR |
0 |
11 |
"," |
1 |
au_id |
2 |
SQLCHAR |
0 |
40 |
"," |
2 |
au_lname |
3 |
SQLCHAR |
0 |
20 |
"," |
3 |
au_fname |
4 |
SQLCHAR |
0 |
12 |
"," |
4 |
phone |
5 |
SQLCHAR |
0 |
40 |
"," |
5 |
address |
6 |
SQLCHAR |
0 |
20 |
"," |
6 |
city |
7 |
SQLCHAR |
0 |
2 |
"," |
7 |
state |
8 |
SQLCHAR |
0 |
5 |
"," |
8 |
zip |
9 |
SQLCHAR |
0 |
1 |
"," |
9 |
contract |
10 |
SQLCHAR |
0 |
0 |
"," |
0 |
age |
11 |
SQLCHAR |
0 |
0 |
"\r\n" |
0 |
salutation |
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, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:
BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat'
WITH (FORMATFILE = 'c:\authors.fmt')