BCP and Identity columns

I am using BCP to inport an ASCII text file into an existing SQL Server database. The SQL Server Database has an identity column, the ASCII text file does not. I have read about the /E switch for BCP and my system does not seem to follow what the /E switch information says. My .fmt file is as follows.
6.0
13
1       SQLCHAR       0       0       ""        1       ID
2       SQLCHAR       0       255     "\t"      2       JOBNUMBER
3       SQLCHAR       0       4       "\t"      3       COSTELEMENT
4       SQLCHAR       0       4       "\t"      4       CLASS
5       SQLCHAR       0       1       "\t"      5       REVOREXP
6       SQLCHAR       0       30      "\t"      6       DESCRIPTION
7       SQLCHAR       0       10      "\t"      7       ABBREVIATION
8       SQLCHAR       0       25      "\t"      8       STANDARDAMOUNT
9       SQLCHAR       0       25      "\t"      9       STANDARDHOURS
10      SQLCHAR       0       1       "\t"      10      ISLABOR
11      SQLCHAR       0       1       "\t"      11      BILLABLE
12      SQLCHAR       0       1       "\t"      12      APPLYFEE
13      SQLCHAR       0       1       "\r\n"    13      APPLYFEEBURDEN

Where ID is the Identity column. Note that I have set the length to 0 and the terminator to null "" so that it isn't looking in the ASCII file for ID information.

The bcp command line looks like:

bcp mait.dbo.CELMWorkfile in e:\mait\copy\CELM.txt /f e:\mait\copy\celm.fmt  /e e:\mait\copy\ercelm.log /F 2 /U sa /P xxxxx S garfield

The error message I keep getting is:

#@ Row 1, Column 1: Attempt to bulk-copy a NULL value into a Server column which does not accept NULL values. @#
      TC7N      LABN      E      TECHNICAL STAFF ASSISTANT      TCH ST AST      0.00      0.00      N      Y      Y      N      <Unable to display>      <Unable to display>

The Identity column is the only one defined in my table as not being able to accept null values.

Any ideas?

Thanks,

Tim      
ttheisAsked:
Who is Participating?
 
mikkonConnect With a Mentor Commented:
I have successfully BCP'd to an identity column but in my case I had an ID column in the text file as well. BCP simply ignored the ID's in the text file and assigned new IDs as the table identity column.

You might also try settings a length (4) for your blank ID line in the FMT file.
0
 
ttheisAuthor Commented:
In further research on my own, I discovered that this is a recognized bug by Microsoft in SQL Versions 6.0 and 6.5. Their solution was to add an ID column to the input ASCII file for the BCP utility to ignore. Indeed, this solution worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.