In this 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:
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 |
0 |
"" |
5 |
address |
6 |
SQLCHAR |
0 |
20 |
"," |
6 |
city |
7 |
SQLCHAR |
0 |
2 |
"," |
7 |
state |
8 |
SQLCHAR |
0 |
0 |
"" |
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. 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:
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 |
0 |
"" |
0 |
address |
6 |
SQLCHAR |
0 |
20 |
"," |
6 |
city |
7 |
SQLCHAR |
0 |
2 |
"," |
7 |
state |
8 |
SQLCHAR |
0 |
0 |
"" |
0 |
zip |
9 |
SQLCHAR |
0 |
1 |
"\r\n" |
9 |
contract |
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')
Note Because address and zip are not present in the data file, those columns will contain NULL in the SQL Server table. Therefore, authors2 must allow null values in those columns.