BULK INSERT with variable file name

Question

i am trying to bulk insert into Db using sql server 2005

Below is the code.

declare @path varchar(500)  set @path = 'E:\Support\test.csv';   Create table #mytable( name varchar(max), class varchar(max), roll varchar(max) )  BULK INSERT #mytable FROM @path <-- Error line WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );  Go  select * from #mytable drop table #mytable 

Problem: issue is that my file path is dynamic and comes from a variable instead of hard coding which is not working If i change the error line to below it works

 BULK INSERT #mytable FROM 'E:\Support\test.csv';  

Please advise how to fix this

Answer 1

Try to use Dynamic SQL:

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