Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4528
  • Last Modified:

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!
0
xenium
Asked:
xenium
  • 2
1 Solution
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now