Bulk Insert with Variable Data

[bmacbmac]

Hello,

I am trying to run several bulk inserts using a 'while' statement. As part of the process I set the @path variable to equal the file I want to bulk insert during this part of the process. In this case the @path = 'd:\2007\0001-0093\EXPORT.IDX'

declare @path varchar(200)
declare @mincounter int

set @mincounter = (select min(counter) from idxlistcounter where processed is not null)
set @path = (select '''' + list + '''' from idxlistcounter where counter = @mincounter)

bulk insert temp
from @path
with (fieldterminator = '","')

I get the error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@path'.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Is there something I can add to the 'from' line to use the @path variable? I need to insert one *.idx file at time (i edit the imported contents before inserting the next *.idx file).

Thanks!

 

[bmacbmac]

I think I figured it out:

declare @path varchar(200)
declare @mincounter int
declare @path2 varchar(200)
declare @bulkinsert varchar(200)

set @mincounter = (select min(counter) from idxlistcounter where processed is not null)
set @path = (select '''' + list + '''' from idxlistcounter where counter = @mincounter)
set @bulkinsert = 'bulk insert temp from ' +@path + 'with (fieldterminator = ''' + '","' + ''')'

exec (@bulkinsert)