Bulk Insert with filename parameter

Question

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'
)

Are you looking for a pure SQL solution, or are you able to use C#/VB? — Paul Alan Taylor, Sep 05 '11 at 10:18
Thanks Paul! I already found something http://stackoverflow.com/questions/4050790/bulk-insert-using-stored-procedure — Ilan, Sep 05 '11 at 10:24
@IIan, if you have the answer please close the question, and next time search SO for similar questions before asking one. — Ash Burlaczenko, Sep 05 '11 at 10:35

Bogdan Sahlean · Accepted Answer · 2011-09-05T12:07:16.263

44

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);

edited Sep 05 '11 at 12:07

answered Sep 05 '11 at 10:27

Bogdan Sahlean

19,000
3
39
56

1

That exactly what I did. Thanks! http://stackoverflow.com/questions/4050790/bulk-insert-using-stored-procedure – Ilan Sep 05 '11 at 10:38
5

What about SQL Injection? – user441365 Jul 17 '17 at 13:48

Bulk Insert with filename parameter

1 Answers1

Linked