Using a Data File with Fields in a Different Order
In some cases, a data file may have fields in an order different from the corresponding columns in the table. For 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:
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 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.
8.0
9
1 SQLCHAR 0 11 "," 1 au_id SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 40 "," 3 au_lname SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 0 20 "," 2 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 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')