带文件名参数的批量插入 [英] Bulk Insert with filename parameter

问题描述

我需要将数千个数据文件加载到 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);

这篇关于带文件名参数的批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！