sql server - Unable to bulk insert from flat file in SQL -

if execute script below, data can inserted table.

create procedure dbo.loaddatafrflatfile  begin   bulk insert persons 'c:\sampledata1.csv' (fieldterminator = '|', rowterminator = '\n') end  return 1  go  exec dbo.loaddatafrflatfile 

however, if put file path input variable, doesn't work.

create procedure dbo.loaddatafrflatfile (     @flatfilepath varchar(255) )  begin      bulk insert persons ' + @flatfilepath + ' (fieldterminator = '|', rowterminator = '\n') end  return 1  go  exec dbo.loaddatafrflatfile @flatfilepath = 'c:\sampledata1.csv' 

the error shown is:

msg 4860, level 16, state 1, procedure loaddatafrflatfile, line 12 cannot bulk load. file " + @flatfilepath + " not exist. 

please help. in advance.

you need use dynamic sql that, e.g.:

create procedure dbo.loaddatafrflatfile ( @flatfilepath varchar(255) ) begin; declare @sql nvarchar(max) = 'bulk insert persons ''' + @flatfilepath + ''' (fieldterminator = ''|'', rowterminator = ''\n'') '; exec sp_executesql @sql; return 1 end; go  exec dbo.loaddatafrflatfile @flatfilepath = 'c:\sampledata1.csv' 

be careful regarding sql injection, make sure need use have permission execute it.

you can white-list , blacklist values variable can contain prior concatenating , executing it.

dynamic sql reference:

• the curse , blessings of dynamic sql - erland sommarskog
• sp_executesql