Bulk Insert with filename parameter
48,876
The syntax for BULK INSERT statement is :
BULK INSERT
[ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
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 =''\n'' )';
EXEC(@sql);
Author by
Ilan
Updated on September 06, 2020Comments
-
Ilan over 3 years
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.
Ilan.
BULK INSERT TblValues FROM @FileName WITH ( FIELDTERMINATOR =',', ROWTERMINATOR ='\n' )