BULK INSERT with variable file name in SET statement

Question

I am trying to do a bulk insert but the @CSVPath is not resolving.

 declare @path varchar(255) 
 set @path = 'C\CSVPath.csv'; 

 BULK INSERT #mytable FROM @CSVPath <-- Error line
 WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );

I've tried

 FROM ''' + @CSVpath + '''

If I hard code the path it works. If I wrap it all in a SET statement and execute it works.

 declare @sql varchar(max)
 set @sql = 'BULK INSERT #mytable FROM ''' + @CSVPath + ''' WITH ...
 exec (@sql)

However, I cannot do it this way and need to it the first method but it doesn't seem to be resolving and cannot figure how to get it to work.

Does this answer your question? [How to cast variables in T-SQL for bulk insert?](https://stackoverflow.com/questions/5019041/how-to-cast-variables-in-t-sql-for-bulk-insert) — eshirvana, Jul 26 '21 at 18:01

Meysam Asadi · Answer 1 · 2021-07-26T19:24:24.030

0

properly When running query dynamics, the compiler stores the values of the variable, but does not normally name the table or address through the variable.

 BULK INSERT #mytable
 FROM 'C:\test.csv'
 WITH
 (
    FIRSTROW = 2, -- as 1st one is header
    FIELDTERMINATOR = ',',  -- field delimiter
    ROWTERMINATOR = '\n',   -- next row
    TABLOCK
 )

edited Jul 26 '21 at 19:24

answered Jul 26 '21 at 18:13

Meysam Asadi

6,438
3
7
17

This doesn't work. I need it to be a variable. and not static. Why does the build insert not allow this? – Jul 26 '21 at 19:02
I edited the answer and stated my inference in the answer. – Meysam Asadi Jul 26 '21 at 19:25

BULK INSERT with variable file name in SET statement

1 Answers1