Solved

BCP and Identity columns

Posted on 1997-12-11
2
1,560 Views
Last Modified: 2006-11-17
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
Comment
Question by:ttheis
2 Comments
 
LVL 1

Accepted Solution

by:
mikkon earned 50 total points
ID: 1089588
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
 

Author Comment

by:ttheis
ID: 1089589
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now