In this example, the New_auth.dat data file (ASCII, or character format) contains the same number of fields as the authors2 table, but the au_lname and au_fname fields are reversed. These fields will be reordered during the bulk copy procedure.
The New_auth.dat file:
777-77-7777,Chris,Smith,303 555-1213,27 College Ave,Denver,CO,80220,1
888-88-8888,John,Doe,206 555-1214,123 Maple Street,Seattle,WA,95099,0
999-99-9999,Jane,Door,406 555-1234,45 East Main,Bozeman,MT,59715,1
To bulk copy data selectively to the correct columns in authors2, 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 (,), 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. 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 further modified with a text editor to change the server column order (sixth field in the format file) of the au_lname and au_fname fields.
7.0
9
1 |
SQLCHAR |
0 |
11 |
"," |
1 |
au_id |
2 |
SQLCHAR |
0 |
40 |
"," |
3 |
au_lname |
3 |
SQLCHAR |
0 |
20 |
"," |
2 |
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 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')