locked
Using a FORMATFILE in a bulk insert within a stored procedure RRS feed

  • Question

  • I have just finished building my first stored proc thanks to the advice given in this forum. After reading Erland's guide to dynamic SQL, I've been working on a new proc which uses a bulk insert statement combined with a format file, but I keep getting an error (Incorrect syntax near 'C:\....\FormatFile.txt'). I believe it is due to something simple like incorrect spacing, but I've tried a few variations and nothing is working.

    Here is my code:

    SET @RAWinsertcmd = N'BULK INSERT RawReader	
         FROM '+ QUOTENAME(@filepath)+N'	
         WITH(FIRSTROW = 0,	
         FIELDTERMINATOR = '','',	
         ROWTERMINATOR = ''\n'',	
         FORMATFILE = '+ QUOTENAME(@formatfile)+N')'
    I've set the @formatfile variable as NVARCHAR(MAX) = 'C:\...\FormatFile.txt'

    The insert command is set to execute after the table is created, and the table creates just fine and when I run the bulk insert outside of the stored proc, it works fine.

    Thanks in advance for the assistance.


    • Edited by Mellonjollie Tuesday, November 20, 2012 5:09 PM clarity
    Tuesday, November 20, 2012 5:06 PM

Answers

  • I got it:

    @RAWinsertcmd NVARCHAR(MAX) = N'BULK INSERT RawReader      

    FROM '+QUOTENAME(@filepath,'''')+N'       WITH(FIRSTROW = 0,       FIELDTERMINATOR = '','',       ROWTERMINATOR = ''\n'',       FORMATFILE = '+QUOTENAME(@formatfile,'''')+N')'


    I used your site to figure out the rest, now I have 9 stored procs that are making my life much easier.

    • Marked as answer by Iric Wen Monday, December 3, 2012 6:39 AM
    Tuesday, November 20, 2012 10:55 PM

All replies

  • The First Commandment of dynamic SQL: PRINT first and TEST script.

    DECLARE @RAWinsertcmd nvarchar(max) = N'BULK INSERT RawReader	
         FROM '+ QUOTENAME('@filepath')+N'	
         WITH(FIRSTROW = 0,	
         FIELDTERMINATOR = '','',	
         ROWTERMINATOR = ''\n'',	
         FORMATFILE = '+ QUOTENAME('@formatfile')+N')'
    
    PRINT @RAWinsertcmd
    /*
    BULK INSERT RawReader	
         FROM [@filepath]	
         WITH(FIRSTROW = 0,	
         FIELDTERMINATOR = ',',	
         ROWTERMINATOR = '\n',	
         FORMATFILE = [@formatfile])
    */

    Test the printed script first, it has to work, don't even try it with dynamic execute until you get the printed version seamlessly working.

    Dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Edited by Kalman Toth Tuesday, November 20, 2012 5:52 PM
    Tuesday, November 20, 2012 5:11 PM
  • That print command is helpful, thanks Kalman. I will investigate further.

    Tuesday, November 20, 2012 5:39 PM
  • I got it:

    @RAWinsertcmd NVARCHAR(MAX) = N'BULK INSERT RawReader      

    FROM '+QUOTENAME(@filepath,'''')+N'       WITH(FIRSTROW = 0,       FIELDTERMINATOR = '','',       ROWTERMINATOR = ''\n'',       FORMATFILE = '+QUOTENAME(@formatfile,'''')+N')'


    I used your site to figure out the rest, now I have 9 stored procs that are making my life much easier.

    • Marked as answer by Iric Wen Monday, December 3, 2012 6:39 AM
    Tuesday, November 20, 2012 10:55 PM
  • The use of quotename() is not correct here. Quotename will by default enclose the vale in brackets, and this is not what you want here. You can use quotename(@filepath, ''''), but beware that if the path is longer than 128 characters or so, it will truncate. You will be safer using quotestring from my article.

    And Kalman is right. Use PRINT!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, November 20, 2012 11:10 PM