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


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
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
Posted Thursday, November 20, 2014 5:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:33 AM
Points: 52, Visits: 457
Lewis, did you ever achieve a resolution for this? I have bumped into the same issue, and only work around is to move files to alternate location or BCP.




Post #1636771
Posted Friday, November 21, 2014 5:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 6:13 PM
Points: 59, Visits: 312
File tables don't allow local memory-mapped file access, which may explain the "request is not supported" error with BULK INSERT. I would expect a technique that performs the bulk insert remotely (e.g. BCP, SISS, etc.) will avoid this restriction with file tables.

Post #1637106
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse