Using a Data File with Fields in a Different Order

Administering SQL Server

Administering SQL Server

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')