Bulk load: An unexpected end of file was encountered in the data file

Question

I am using SQL Server Express 2008

When I'm trying load data from txt file in to this table

create table Clients
(
ClientID int not null IDENTITY (9000,1),
LastName varchar (30)not null,
FirsName varchar (30)not null,
MidInitial varchar (3),
DOB date not null,
Adress varchar (40) not null,
Adress2 varchar (10),
City varchar (40) not null,
Zip int not null,
Phone varchar (30) ,
CategCode varchar (2) not null,
StatusID int  not null,
Hispanic BINARY default 0,
EthnCode varchar(3) ,
LangID int,
ClientProxy varchar (200),
Parent varchar (40),
HshldSize int default 1,
AnnualHshldIncome INT,
MonthlyYearly VARCHAR(7) ,
PFDs INT,
WIC BINARY default 0,
Medicaid BINARY default 0,
ATAP BINARY default 0,
FoodStamps BINARY default 0,
AgencyID int not null,
RoutID int  ,
DeliveryNotes varchar (200),
RecertificationDate date not null,
Notes text,
Primary Key (ClientID)
);

I use

SET IDENTITY_INSERT Clients2 ON;
BULK INSERT Clients2
FROM 'c:\Sample_Clients.txt'  
WITH
(
   FIELDTERMINATOR = ',', 
   ROWTERMINATOR = '\r\n'  
)

SQL Server Express trows me errors

Msg 545, Level 16, State 1, Line 2 Explicit value must be specified for identity column in table 'Clients' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

File has only one line (for now just sample data) I check it many times its one line

Data looks like this

13144,Vasya,Pupkin,,1944-10-20,P.O. Box 52,,Wrna,99909,[phone],SR,4,0,W,1,,,3,1198,month,0,0,1,0,1,45,,,2011-04-27

Any ideas how to fix this problem?

Answer 1

You need the parameter KEEPIDENTITY in your bulk insert statement. This is required to retain identity values in the load.

BULK INSERT Clients2 FROM 'c:\Sample_Clients.txt'
    WITH ( KEEPIDENTITY, FIELDTERMINATOR = ',', ROWTERMINATOR = '\r\n'
)

I also think you will have a problem because you have no data or placeholder for the Notes column. A comma added to the end of the file should address this.