How to solve this error regarding bulk insert in sql server?

See more:

SQL-Server

Hello,

I am filling database table from multiple txt file but it's showing eror, my datatable did not fill.

CREATE TABLE #files (name varchar(200) NULL, sql varchar(7000) NULL)

INSERT #files(name)
   exec master..xp_cmdshell 'dir /b C:\AdminDBBOM\*.TXT'

DELETE #files WHERE coalesce(name, '') NOT LIKE '%.TXT%'

UPDATE #files
SET   sql  = 'BULK INSERT BaaN_BOM FROM ''' + name + ''' WITH (' +
             'FIRSTROW=2, FIELDTERMINATOR = ''|'',' +
             'ROWTERMINATOR = ''\n'')'

DECLARE @sql varchar(8000)

DECLARE cur CURSOR STATIC LOCAL FOR
   SELECT sql FROM #files

OPEN cur

WHILE 1 = 1
BEGIN
   FETCH cur INTO @sql
   IF @@fetch_status <> 0
      BREAK

   EXEC(@sql)
END

DEALLOCATE cur

Error:-
(6 row(s) affected)

(2 row(s) affected)

(10 row(s) affected)
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "1.TXT" does not exist.
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "2.TXT" does not exist.
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "3.TXT" does not exist.
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "4.TXT" does not exist.

How can be possible?
Please help me.

Thank You.
Ankit Agarwal
Software Engineer