Post reply

Bulk Insert Error

  • December 22, 2006 at 10:37 am

    #115842

    Hello,

    I am sure that others have had the problem, I just can't find the answer out there.

    I have a bulk insert statement that works fine when I run it in toad as:

    Bulk Insert tmpjournal

    FROM '\\symixap2\c$\filename.csv'

    With (fieldterminator = ',', rowterminator = '\n')

    Now the problem occurs when I put the statement in a stored procedure and I need to pass the "from" value as a parameter. Users will input the file name as needed. I create the procedure with a variable (@pathfilename) to hold user input. The value I pass for @pathfilename is '\\symixap2\c$\filename.csv' .So now the bulk insert looks like this ( inside the proc)

    Bulk Insert tmpjournal

    FROM '"+@pathfilename+"'

    With (fieldterminator = ',', rowterminator = '\n')

    Now the bulk insert fails with the following error. +@pathfilename+ does not exist. Any help would be appreciated greatly.

    Thanks,

    S

     

  • December 22, 2006 at 11:09 am

    #679802

    Anytime you try to put a row source or a table name in a FROM, you must (unfortunately) use dynamic SQL...

    DECLARE @sql VARCHAR(8000)

        SET @sql = '

    BULK INSERT tmpJournal

           FROM ''' + @pathfilename + '''

           WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2006 at 11:43 am

    #679807

    Thanks, for the info, I made the changes and now I am getting this error:

    "Could not find stored procedure 'Bulk Insert ...

    What did I miss

    Thanks again

  • December 22, 2006 at 11:47 am

    #679808

    Jeff ,

    I figured it out, I put the set @sql between a begin and an end and excuted via

    EXEC (@sql) and it works. For the first time in days something works.

    Thanks again

  • December 23, 2006 at 7:26 am

    #679878

    Crud... copy and paste error on my part... I didn't include the exec.  Glad you got it working and thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 14, 2008 at 11:38 am

    #767777

    Nice post... I've been working on this for an hour or so and this post was the money!!

    Thanks

  • January 14, 2008 at 4:07 pm

    #767921

    Thanks for the feedback, Chris... glad it helped.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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