BULK INSERT with variable file name

Question

i am trying to bulk insert into Db using sql server 2005

Below is the code.

declare @path varchar(500) 
set @path = 'E:\Support\test.csv'; 

Create table #mytable( name varchar(max), class varchar(max), roll varchar(max) )

BULK INSERT #mytable FROM @path <-- Error line
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); 
Go 
select * from #mytable
drop table #mytable

Problem: issue is that my file path is dynamic and comes from a variable instead of hard coding which is not working If i change the error line to below it works

 BULK INSERT #mytable FROM 'E:\Support\test.csv';

Please advise how to fix this

Does this answer your question? [How to cast variables in T-SQL for bulk insert?](https://stackoverflow.com/questions/5019041/how-to-cast-variables-in-t-sql-for-bulk-insert) — wibeasley, Aug 07 '20 at 22:41

score 47 · Accepted Answer · edited Oct 28 '13 at 08:47

47

Try to use Dynamic SQL:

declare @sql varchar(max)
set @sql = 'BULK INSERT #mytable FROM ''' + @path + ''' WITH ...
exec (@sql)

edited Oct 28 '13 at 08:47

Peter

37,042
39
142
198

answered Apr 05 '11 at 05:07

Andomar

232,371
49
380
404

4

For future users, notice that there are 3 quotes before/after the variable. It took me a while to make it work because I was only adding 2 – Berni Mar 26 '14 at 21:26
Shoule be `'BULK INSERT #mytable FROM ' + QUOTENAME(@path, '''') + ' WITH ...` to correctly escape the file name. – Charlieface Jul 09 '23 at 22:13

score 11 · Answer 2 · edited Dec 05 '13 at 11:36

11

DECLARE @path varchar(50) = 'D:\ARQUIVOS_CARGAS\CABOS\FILE.prn'
DECLARE @SQL_BULK VARCHAR(MAX)
SET @SQL_BULK = 'BULK INSERT #TAB FROM ''' + @path + ''' WITH
        (
        CODEPAGE = ''ACP'',
        FIRSTROW = 1,
        FIELDTERMINATOR = ''tab'',
        ROWTERMINATOR = ''0x0a'',
        KEEPNULLS
        )'

EXEC (@SQL_BULK)

edited Dec 05 '13 at 11:36

Taryn

242,637
56
362
405

answered Dec 05 '13 at 11:26

Philip

127
2
6

BULK INSERT with variable file name

2 Answers2

Linked

Related