Almost there with bulk insert

Giganews Newsgroups
Subject: Almost there with bulk insert
Posted by:  Ted (r.ted.bye…@rogers.com)
Date: 14 Aug 2006

I have BULK INSERT T-SQL statements that work for all of my basic data
tables except for one.

Here is the problem statement (with obvious replacement of irrelevant
path info):

BULK INSERT igbconts_tmp FROM 'C:\my_code_path\IGBCONTS.txt'
    WITH (KEEPNULLS,
          FORMATFILE = 'C:\my_data_path\contacts.fmt');

And here is the output from this statement:

Msg 8152, Level 16, State 14, Line 3
String or binary data would be truncated.
The statement has been terminated.

(0 row(s) affected)

This tells me precisely nothing about where the real problem lies.  I
am reluctant to post either the table definition or the format file
since they are large (the table, and thus the data file, has 104
fields.  However, the first few lines in the format file are:

8.0
105
1 SQLCHAR 0 0 "\"" 0 dummy ""
2 SQLCHAR 0 0 "\",\"" 1 contact_id ""
3 SQLCHAR 0 0 "\",\"" 2 full_name ""
4 SQLCHAR 0 0 "\",\"" 3 last_name ""

And here are the last couple lines:

104 SQLCHAR 0 0 "\",\"" 103 user_defined_field15 ""
105 SQLCHAR 0 0 "\"\r
" 104 user_defined_field16 ""

The table was created using the string length information given to us
by the data provider, and those fields that are not strings consist of
a few datetime values and a moderate number of floating point numbers.

The message suggests to me that one of the fields is too small for what
was actually found in the corresponding column in the data file for at
least one record.  But in addition to there being over 100 columns,
there are several thousand records in the data file!

How do I determine precisely where the problem lies?

Thanks,

Ted

Replies