I have difficulties with bulk insert.
If I have a .txt file with 1 element per row it work fine, see below:
SP:
Code:
CREATE procedure MyTest
as
CREATE TABLE Test
(
jobnumber char(15)
)
DECLARE @SQL varchar(2000),
@PathFileName varchar(200)
set @PathFileName = 'c:\yak.txt'
BULK INSERT Test
FROM 'c:\yak.txt' WITH ( FIELDTERMINATOR = ',', DATAFILETYPE =
'char')
EXEC (@SQL)
select * from Test
GO
.TXT Table:
ABCDEFGHIJKLMN
CCCCCCCCC
BBBB
MMMMMMMMM
Now I add a 2nd field to my table (jobphase) and change my .txt table as well, it does not work:
SP:
Code:
CREATE procedure MyTest
as
CREATE TABLE Test
(
jobnumber char(15),
jobphase char(15)
)
DECLARE @SQL varchar(2000),
@PathFileName varchar(200)
set @PathFileName = 'c:\yak.txt'
BULK INSERT Test
FROM 'c:\yak.txt' WITH ( FIELDTERMINATOR = ',', DATAFILETYPE =
'char')
EXEC (@SQL)
select * from Test
GO
.TXT table:
ABCDEFGHIJKLMN,AAAAAAAA,
CCCCCCCCC,GGGGGGGGG,
BBBB,TTTTTTTTT,
MMMMMMMMM,YYYYYYYY,
Get error:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.