SQL 批量加载：在数据文件中遇到意外的文件结尾
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14945803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk load: An unexpected end of file was encountered in the data file
提问by Andrey
I am using SQL Server Express 2008
我正在使用 SQL Server Express 2008
When I'm trying load data from txt file in to this table
当我尝试将 txt 文件中的数据加载到此表中时
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
SQL Server Express 向我抛出错误
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.
消息 545，级别 16，状态 1，第 2 行 当 IDENTITY_INSERT 设置为 ON 或复制用户插入 NOT FOR REPLICATION 标识列时，必须为表“Clients”中的标识列指定显式值。
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?
任何想法如何解决这个问题？
回答by Jeff Siver
You need the parameter KEEPIDENTITY in your bulk insert statement. This is required to retain identity values in the load.
您需要在批量插入语句中使用参数 KEEPIDENTITY。这是在负载中保留标识值所必需的。
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.
我还认为您会遇到问题，因为您没有 Notes 列的数据或占位符。添加到文件末尾的逗号应该解决这个问题。
回答by Osmar_Jr
Check the number of columns in the file, if you have a single line with more or less columns break bulkinsert.
检查文件中的列数，如果您有一行或多或少的列会中断批量插入。
