Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1632
  • Last Modified:

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      
0
ttheis
Asked:
ttheis
1 Solution
 
mikkonCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now