带文件名参数的批量插入

2021-12-13 00:00:00 bulkinsert sql-server-2005 tsql sql-server-2008 sql-server

我需要将数千个数据文件加载到 SQL Server 表中.所以我写了一个只接收一个参数——文件名的存储过程.但是..以下不起作用..编译器"抱怨@FileName参数..它只想要纯字符串..像'file.txt'.提前致谢.

I need to load a couple of thousands of data files into SQL Server table. So I write a stored procedure that receives just one parameter - file name. But.. The following doesn't work.. The "compiler" complains on @FileName parameter.. It wants just plain string.. like 'file.txt'. Thanks in advance.

伊兰.

BULK INSERT TblValues
FROM @FileName
WITH 
(
FIELDTERMINATOR =',',
ROWTERMINATOR ='
'
)

推荐答案

BULK INSERT 语句的语法是:

BULK INSERT 
   [ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH

所以，文件名必须是字符串常量.要解决问题，请使用动态 SQL:

So, the file name must be a string constant. To solve the problem please use dynamic SQL:

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT TblValues FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''
'' )';
EXEC(@sql);

相关文章