How to Correctly Cast Variables for Bulk Inserts in T-SQL?

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.

The above is the detailed content of How to Correctly Cast Variables for Bulk Inserts in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!