Post reply

BULK INSERT WITH PARAMETERS - HELP!!!!

  • 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

     

     

  • 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.

  • November 5, 2004 at 5:07 am

    #529072

    Thx that worked great!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply