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.

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. — Arthur, Sep 13 '21 at 14:12
And that's why PostgreSQL is so nice: weirdly obtuse corner case like this just get *fixed*, not left there to fester. — Contango, Sep 14 '21 at 13:29

score 17 · Accepted Answer · edited Nov 01 '15 at 11:25

17

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)

edited Nov 01 '15 at 11:25

Contango

76,540
58
260
305

answered Feb 16 '11 at 16:37

pcofre

3,976
18
27

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. – Contango Feb 16 '11 at 17:23
6

What about SQL Injection? – user441365 Jul 17 '17 at 13:49
2

All of the answers seem to ignore SQL Injection. Is there no way to parameterise the dynamic SQL for bulk insert? – Jimbo Sep 21 '18 at 13:57

score 3 · Answer 2 · answered Feb 16 '11 at 16:25

3

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

answered Feb 16 '11 at 16:25

Jason

4,557
5
31
40

3

What about SQL Injection? – user441365 Jul 17 '17 at 13:49
Exactly. Surely there is a way to do this using sp_ExecuteSQL with parameters? – Jimbo Sep 21 '18 at 13:51

score 2 · Answer 3 · answered Feb 16 '11 at 16:30

2

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)

answered Feb 16 '11 at 16:30

Nick Kavadias

7,542
2
37
46

4

What about SQL Injection? – user441365 Jul 17 '17 at 13:49

score 1 · Answer 4 · answered Feb 16 '11 at 16:27

1

A string literal is required.

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

You could use dynamic sql to generate the string literal.

answered Feb 16 '11 at 16:27

dotjoe

26,242
5
63
77

score 0 · Answer 5 · answered Aug 19 '14 at 01:03

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);

What about SQL Injection? – user441365 Jul 17 '17 at 13:49 — user441365, Jul 17 '17 at 13:49

score -1 · Answer 6 · answered Feb 16 '11 at 16:25

-1

Can you try FROM ' + @CSVfile + '

answered Feb 16 '11 at 16:25

Sachin Shanbhag

54,530
11
89
103

See "[answer]" and [Explaining entirely code-based answers](https://meta.stackoverflow.com/q/392712/128421)". 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. – the Tin Man Nov 28 '22 at 19:34

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

6 Answers6

Linked

Related