How to cast variables in T-SQL for bulk insert?

Question

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

-- Bulk insert data from the .csv file into the staging table.
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 will not work
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2    
)

The error is:

Incorrect syntax near the keyword 'with'. 

If I replace:

FROM @CSVfile

with:

FROM 'T:\x.csv'

... then it works nicely.

Naze

I find it astonishing that the BULK INSERT syntax of SQL Server fails to accept an expression that evaluates to an SQL string following the keyword FROM. How random. – Brutal

And that's why PostgreSQL is so nice: weirdly obtuse corner case like this just get fixed, not left there to fester. – Naze

Answer 1

As I know only literal string is required in the from. In that case you have to write a dynamic query to use bulk insert

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

Lionfish

Brilliant, this worked with just a few modifications. It didn't seem to work if I used any double quotes in the query at all, your solution is pretty much perfect. – Naze

What about SQL Injection? – Fizgig

All of the answers seem to ignore SQL Injection. Is there no way to parameterise the dynamic SQL for bulk insert? – Construe

Answer 2

Have you tried with dynamic SQL?

SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "

and then

EXEC(@SQL)

Ref.: http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

Toxinantitoxin

What about SQL Injection? – Fizgig

Exactly. Surely there is a way to do this using sp_ExecuteSQL with parameters? – Construe

Answer 3

you have to engage in string building & then calling EXEC() or sp_executesql BOL has an example:

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>'' 
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Clarence

What about SQL Injection? – Fizgig

Answer 4

A string literal is required.

http://msdn.microsoft.com/en-us/library/ms188365.aspx

You could use dynamic sql to generate the string literal.

Edacity

Answer 5

Most of the time the variable i'm looking for in a file name is the date, and this one works perfectly for bulk inserting files with date, for use such as in a daily job. Change as per your need, date format, table name, file path, file name and delimiters.

    DECLARE @DT VARCHAR (10)
    DECLARE @INSERT VARCHAR (1000)
    SET @DT = (CONVERT(VARCHAR(10),GETDATE()-1,120))
    SET @INSERT = 'BULK INSERT dbo.table FROM ''C:\FOLDER\FILE'+@DT+'.txt'''+' WITH  (FIRSTROW=2, FIELDTERMINATOR=''\t'', ROWTERMINATOR=''\n'')'
    EXEC (@INSERT);

Manometer

What about SQL Injection? – Fizgig

Answer 6

Can you try FROM ' + @CSVfile + '

Shoshanashoshanna

See "How to Answer" and Explaining entirely code-based answers". While this might be technically correct, it doesn't explain why it solves the problem or should be the selected answer. We should educate along with helping solve the problem. – Tish