locked
Stored Procedure BULK INSERT RRS feed

  • Question

  • Firstly, I am new to sql so please excuse my ignorance!

    I have a stored procedure that tests the import of about 100 text files.  This is to screen for file errors prior to processing.  The results for each file are held in a table.  The procedure script is as follows:

    <snip>

    insert into st_fileimport (dealernumber, handheld_file)
    values ('01', 'Fail')

    BULK INSERT dbo.filetesthandheld
    FROM 'c:\inetpub\wwwroot\S_Data\dealers\01\foo.csv'
    WITH
    (
    FIELDTERMINATOR = ',' ,
    ROWTERMINATOR = '\n' ,
    ROWS_PER_BATCH = 500
    )

    update st_fileimport
    set handheld_file = 'Pass' where dealernumber = '01'

    </snip>

    This is then repeated for each file.  This works fine, but as soon as an error is encountered the script terminates which means that you need to re-run this procedure iteratively until all files pass.

    Is there a way of structuring the stored procedure so it will fail on one import and then move onto the next?  Or, a better methodology all together?

    Any help or advise would be greatly appreciated.

    Karen

    Thursday, January 26, 2006 10:14 AM

Answers