There’s nothing wrong with your stored procedure code – the point is: the BULK INSERT
command cannot accept a file name as a variable.
This does work:
BULK INSERT ZIPCodes
FROM 'e:\5-digit Commercial.csv'
WITH
but this never works – within a stored proc or not:
DECLARE @filename VARCHAR(255)
SET @filename="e:\5-digit Commercial.csv"
BULK INSERT ZIPCodes
FROM @filename
WITH
So you just cannot do it this way, unfortunately. You could consider building up your BULK INSERT
statement as a string (with a fixed file name) and then execute it as dynamic SQL – but I don’t really see any other solution.
DECLARE @filepath nvarchar(500)
SET @filepath = N'e:\5-digit Commercial.csv'
DECLARE @bulkinsert NVARCHAR(2000)
SET @bulkinsert =
N'BULK INSERT ZIPCodes FROM ''' +
@filepath +
N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
EXEC sp_executesql @bulkinsert