如何在sql server中解决有关批量插入的错误？ [英] How to solve this error regarding bulk insert in sql server?

问题描述

您好，



我从多个txt文件填充数据库表但是它显示错误，我的数据表没有填充。

< pre lang =sql> 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％'

更新 #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

错误： -

（6排受影响）



（2排受影响）



（10行受影响）

消息4860，等级16，状态1，行1

无法批量加载。文件1.TXT不存在。

消息4860，等级16，状态1，行1

无法批量加载。文件2.TXT不存在。

消息4860，等级16，状态1，行1

无法批量加载。文件3.TXT不存在。

消息4860，等级16，状态1，行1

无法批量加载。文件4.TXT不存在。



怎么可能？

请帮助我。



谢谢。

Ankit Agarwal

软件工程师

解决方案

错误消息非常具体：

无法批量加载。文件1.TXT不存在。

最可能的原因是

1）文件不一样计算机作为SQL服务器实例（它必须是，除非文件夹与SQL计算机共享，但它们不会被视为C：\为您的文件）

2）如果文件位于正确的计算机上，则该文件夹可能没有SQL Server在其执行的用户ID下访问它们所需的权限。看看这里：用C＃备份SQL数据库 [ ^ ]在那么，它现在可以工作吗？部分。 - 它应该有帮助。

此错误定义了该关注文件是否不在定义的位置。或者，如果您使用sql server的实例，则可能无法从本地PC插入。请尝试将您的操作转移到主sql server运行系统的位置。

http://stackoverflow.com/questions/10016799/sql-bulk-insert-file-does-not-exist [ ^ ]

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

解决方案

The error message is pretty specific:

Cannot bulk load. The file "1.TXT" does not exist.

The most likely reasons are that either
1) The files are not on the same computer as the SQL server instance (which it would have to be, unless the folder is shared to the SQL computer, but them you wouldn't be looking as "C:\" for your files)
2) If the files are on teh right computer, then the folder probably does not have the required permissions for SQL server to access them under the user ID it is executing as. Have a look here: Backing up an SQL Database in C#[^] under the section "So, It'll Work Now?" - it should help.

This Error defined whether that concern file is not in defined location. Or if you using an instance of sql server you may unable to insert from your local PC. please try your action into where that main sql server running system.

http://stackoverflow.com/questions/10016799/sql-bulk-insert-file-does-not-exist[^]

这篇关于如何在sql server中解决有关批量插入的错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！