Pass the name of the file to a bulk insert

Question

I'm trying to pass the name of a file via a variable to bulk insert/

This does not work:

bulk insert XMLfile 
from 
@Name_of_file
with ( rowterminator = ''' + char(10) + ''' )

But, this does:

bulk insert XMLfile 
from 
'abc.xml'
with ( rowterminator = ''' + char(10) + ''' )

How would I go about passing the file name to this statement?

Answer 1

You can't use a variable for the file name. If you look at BULK INSERT syntax, you will see that file name is a constant. To achieve what you are looking for, you have to use dynamic SQL, i.e. building the query as a string.