0

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,(907) 111-1111,SR,4,0,W,1,,,3,1198,month,0,0,1,0,1,45,,,2011-04-27

Any ideas how to fix this problem?

CuberChase
  • 4,458
  • 5
  • 33
  • 52
Andrey
  • 1,629
  • 13
  • 37
  • 65
  • 1
    Welcome to StackOverflow: if you post code, XML or data samples, **P L E A S E** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 18 '13 at 21:48
  • [*`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead.*](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Feb 18 '13 at 21:49
  • 2
    Your table has **30 columns** - the sample from the file you bulk load has **29 columns** ..... also: did you turn `IDENTITY_INSERT ON` for your table **before** bulk loading? – marc_s Feb 18 '13 at 21:51
  • 1
    To re-phrase marc_s's comment: 1 column is missing from your file, and you cannot insert values into an identity column without using `SET IDENTITY_INSERT ON`. If you fix both those issues, what happens? – Pondlife Feb 18 '13 at 21:59
  • marc_s Thanx it was the problem because last field was null it has not data in cvs file and when i save it as txt it just skip that column. I add additional comma after last record and it submits and respond (0) rows affected and wen i did select * from Clients2 it is completely empty – Andrey Feb 18 '13 at 22:00
  • right now it throwing 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. If i used I use SET IDENTITY_INSERT Clients2 ON, if i am using I use SET IDENTITY_INSERT Clients2 OFF it just saed (0 row(s) affected) and did not take any data from file – Andrey Feb 18 '13 at 23:11

1 Answers1

9

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.

Jeff Siver
  • 7,434
  • 30
  • 32
  • 1
    hmm does not work (0 row(s) affected) and after select the table is empty – Andrey Feb 18 '13 at 23:31
  • i add data in that field actualy i add one more extra commas at the end. also right now I am trying to submit data as insert into table and it still trowing me error but different. – Andrey Feb 18 '13 at 23:35
  • Your data and the table layout aren't lining up; I'm not sure where the problem is. Also, why are you using BINARY datatype? I'm guessing those columns should be BIT instead. – Jeff Siver Feb 18 '13 at 23:35
  • I already change it for bit and change data in file it looks like this right now 13144,Vasya,Pupkin,,1944-10-20,P.O. Box 52,,Wrna,99909,(907) 111-1111,SR,4,0,W,1,,,3,1198,month,0,0,1,0,1,45,,,2011-04-27, – Andrey Feb 18 '13 at 23:38
  • it is work but for dome reason it read only 4 first records in file and i have 8000 of them :) – Andrey Feb 19 '13 at 00:38
  • I'd guess there is something wrong with the records in the file. You need to check them all very carefully. – Jeff Siver Feb 19 '13 at 01:29
  • Thanx Jeff yes there was some inconsistanse with records. Thank you you were supper helpful! – Andrey Feb 19 '13 at 02:08