Bulk Insert with variable

1

I have many excel generated csv files which I want to import in to ms sql using bulk insert as a stored procedure. I can do individual files using this.

BULK INSERT HorseRacingMasterTable
FROM 'F:\HorseDb\659620.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

But what I want to do and can't is use a variable for the file name. I.e. in the above example the file name 659620.csv would be replaced with a variable that would be passed to the stored procedure from a vb front end. All and any help much appreciated thank you.

2

You would need to use dynamic SQL. For example:

DECLARE @file_name varchar(500)
DECLARE @sql varchar(8000)

SET @file_name = 'F:\HorseDb\659620.csv'

SET @sql = '
BULK INSERT dbo.HorseRacingMasterTable 
FROM ''' +@file_name + ''' 
WITH 
(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)'

--PRINT @sql
EXEC(@sql)
1 Like
3

Scott thank you so much. It works splendidly as is,and took only a wee bit of thinking and amendment to be able to pass in a parameter from my vb front end. Many thanks.

4

I would want to safeguard against the (presumably incredibly unlikely) possibility that there is a single quote in the filename (or someone is attempting some SQL Injection)

SET @sql = '
...
FROM ''' +REPLACE(@file_name, '''', '''''') + ''' 
...
5

Hi Kristen. Thanks for taking the time to consider this. Wise words which I have acted upon. Thank you.