Casting Variables for Bulk Insert in T-SQL

In T-SQL, the BULK INSERT statement allows data to be imported from an external file into a table. However, if using a string variable as the data source, an error may occur.

Problem:

The following T-SQL code fails with an error (part of a stored procedure):

DECLARE @CSVfile nvarchar(255);
SET @CSVfile = N'T:\x.csv';
BULK INSERT [dbo].[TStagingTable]
-- FROM N'T:\x.csv' -- This line works
FROM @CSVfile -- This line fails
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)

Error message:

Incorrect syntax near the keyword 'with'.

Reason:

In the BULK INSERT statement, the FROM clause requires a literal string as the input file path. When using a variable, it must be cast to a literal string.

Solution:

To cast the variable @CSVfile to a literal string, use the following dynamic T-SQL syntax:

declare @q nvarchar(MAX);
set @q=
    'BULK INSERT [TStagingTable]
    FROM ' char(39) @CSVfile char(39) '
    WITH
    (
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n'',
    FIRSTROW = 1  
    )'
exec(@q)

This code generates a dynamic SQL statement that casts @CSVfile to a literal string within the FROM clause, resolving the issue and allowing the bulk insert to succeed.