Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Bulk Insert using UNC path of a Filetable Directory Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 2:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:17 AM
Points: 6, Visits: 37
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

Post #1476785
Posted Tuesday, July 23, 2013 2:06 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
That's great to hear. Note that the article suggests other alternatives that are faster than the simple version you tried.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1476786
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse