BULK INSERT WITH PARAMETERS - HELP!!!!

• Andrew Dove

  SSC Veteran

  Points: 280

  More actions

  November 4, 2004 at 10:57 am

  #61362

  Hi,

  I am trying to loop an import through a number of different file names using a parameter to change the filename each time, code is below. It works fine when I type in the full location but when I pass it a parameter  it just crashes!!

  Please can someone help me.........

   

  --Scan the directory for files and retrieve filenames

  execute LoadFileDetailsIntoTable '\\warehouse\systems\dialler\'

  DECLARE @NumFiles INT

  SET @NumFiles = (SELECT COUNT(*) FROM ANDREW.DBO.FilesInDir)

  DECLARE @CurrNum INT

  SET @CurrNum = 1

  DECLARE @FILENAME VARCHAR(100)

  SET @FILENAME=(SELECT TOP 1 [FileName] FROM ANDREW.DBO.FilesInDir)

  DECLARE @DIRNAME VARCHAR(100)

  SET @DIRNAME=(SELECT TOP 1 Filepath FROM ANDREW.DBO.FilesInDir)

  DECLARE @FULLPATH VARCHAR(100)

  SET @FULLPATH=RTRIM(@DIRNAME)+RTRIM(@FILENAME)

  --Clear Out Import Table

  DELETE FROM ANDREW.DBO.CSVIMPORT

  --Loop for multiple files

  WHILE @CurrNum<=@NumFiles

  BEGIN

   BULK INSERT Andrew.dbo.csvimport

      FROM  @FULLPATH --This Does not

   --FROM '\\warehouse\systems\dialler\Freedom.txt' --This works

      WITH (FIELDTERMINATOR = ',')

   

  SET @CurrNum = @CurrNum+1

  END

   

   

• Dave-3000

  SSCertifiable

  Points: 6186

  More actions

  November 4, 2004 at 11:56 am

  #528974

  Syntactically, you can't specify the source file that way.  If you want the source of the source file to be a variable, you have to build a string of the bulk insert statement and execute it, like so:

  DECLARE @NString nvarchar(1000)

  SET @NString = N'BULK INSERT Andrew.dbo.csvimport FROM  ''' + @FullPath + N''' WITH (FIELDTERMINATOR = '',''')

  EXEC sp_executesql @NString

  There is no "i" in team, but idiot has two.

• Andrew Dove

  SSC Veteran

  Points: 280

  More actions

  November 5, 2004 at 5:07 am

  #529072

  Thx that worked great!