Using variable to define BULK INSERT file path

Hi,

this is probably a noddy question.. how do i provide the BULK INSERT statement with the data_file info as a variable, ie replace '..mypath..myfile.txt' with eg @Filepath (if i do this i get error  Incorrect syntax near '@Fullpath'.)

BULK INSERT dev.dbo.tmpFileHeader FROM '..mypath..myfile.txt' with (Lastrow=1)

thanks!
xeniumAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need dynamic sql:

declare @sql varchar(8000)
set @sql = 'BULK INSERT dev.dbo.tmpFileHeader FROM ''' + @FilePath + ''' with (Lastrow=1)'
exec (@sql)
0
 
xeniumAuthor Commented:
Great, thanks!
0
 
xeniumAuthor Commented:
For my reference, this in an sp:

CREATE PROCEDURE sp_GetFileheader @Filepath varchar(500) AS

drop table tmpFileHeader
create table tmpFileHeader (HeaderText char(100))

declare @sql varchar(8000)
set @sql = 'BULK INSERT dev.dbo.tmpFileHeader FROM ''' + @FilePath + ''' with (Lastrow=1)'
exec (@sql)
select * from tmpFileHeader


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.