Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

Using a Data File with More Fields

SQL Server 2000

Using a Data File with More Fields

In some cases, a data file may have more fields than there are columns in the table. For example, the New_auth.dat data file (ASCII, or character format) contains two fields (age and salutation) not contained on authors2. 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:

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 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:

8.0
11
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  ","      9  contract    SQL_Latin1_General_Cp437_BIN
10 SQLCHAR  0  0  ","      0  age         SQL_Latin1_General_Cp437_BIN
11 SQLCHAR  0  0  "\r\n"   0  salutation  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')
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.