• Erland,

    I decided to try your approach and in the preliminary tests the results are better than I expected.

    A simplified version of my script is as follows:

    --Beginning of sample script---

    DECLARE @File_Content varchar(MAX), @Delimiter varchar(10)

    SET @Delimiter = CHAR(10)

    SELECT @File_Content = (convert(varchar(MAX), file_stream))

    FROM [dbo].[FileTableTb]

    INSERT INTO vw_File_Load

    SELECT file_record

    FROM [dbo].[udf_text_list_to_tbl](@File_Content, @Delimiter)

    --End of Sample Script---

    -----Function (modified slightly from your website page)-------

    CREATE FUNCTION udf_text_list_to_tbl (@list varchar(MAX), @Delimiter varchar(10))

    RETURNS @tbl TABLE (file_record varchar(MAX) NOT NULL) AS

    BEGIN

    DECLARE @pos int,

    @nextpos int,

    @valuelen int

    SELECT @pos = 0, @nextpos = 1

    WHILE @nextpos > 0

    BEGIN

    SELECT @nextpos = charindex(@Delimiter, @list, @pos + 1)

    SELECT @valuelen = CASE WHEN @nextpos > 0

    THEN @nextpos

    ELSE len(@list) + 1

    END - @pos - 1

    INSERT @tbl (file_record)

    VALUES (substring(@list, @pos + 1, @valuelen))

    SELECT @pos = @nextpos

    END

    RETURN

    END

    --End of Function-----

    Numbers for comparison:

    File size - 406,243 KB

    Records - 838,695

    Timings:

    BULK LOAD - 1 minute 9 seconds

    SAMPLE SCRIPT - 1 minute 33 seconds

    In conclusion, your method simplifies the security model for the application and is cleaner than my work around. While a little slower, I am going to give it a try.

    Thanks for your assistance.

    Louis