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