VERY large binary import/export headache

Hi,

I am currently importing (and exporting) binary flat files to and from Db fields using the TEXTPTR and UPDATETEXT (or READTEXT for export) functions. This allows me to fetch/send the data in manageable packet sizes without the need to load complete files into RAM first.

Given that some files can be up to 1Gb in size I am keen to find out a new way of doing this since the announcement that TEXTPTR, READTEXT and UPDATETEXT are going to be removed from T-SQL.

I had a quick foray into SSIS but couldn't find anything suitable which brings me back to T-SQL. If anyone knows a nice elegant way of doing this and is prepared to share, that would be grand.

Thanks for your time,
Paul

Answer this question

VERY large binary import/export headache

danhood
The "image" type is basically been changed to "varbinary(max)". You no longer need to use the pointer method to access the data. You can use standard string methods to go to a position and insert, etc.

See the BOL under "Using Large-Value Data Types".
Martin Gentry
Thanks to all three of you for posting - you are all right and I shall be (when I have a spare moment!) testing to see which methods work best for me - I'll probably come back and post my findings here...

Many thanks,
Paul
S&#233;bastien Nunes

Do you find the information in BOL insufficient Check the following topic for starters:

http://msdn2.microsoft.com/en-us/library/a1904w6t.aspx

There are other topics that describe the new TSQL and client-side features. If you still have questions then please post back here. Also, if your files are very large then you may not want to store it in the database - it will be very inefficient depending on what you do with the data. Please take a look at the research paper below for some findings on when to store blob data in database:

http://research.microsoft.com/research/pubs/view.aspx msr_tr_id=MSR-TR-2006-45
grolich
SqlServer 2005 has a new .write extension to the UPDATE statement, I haven't used it, but you may try to take a look at it.

Here a short example from the book "Pro SQL Server 2005":

create table testBIGtext
(
testBIGtextId int PRIMARY KEY,
value varchar(max)
)
insert into testBIGtext
values(1,'')
go

DECLARE @offset int
SET @offset = 0
WHILE @offset < 26
BEGIN
UPDATE testBIGtext
--the text I am writing is just starting at the letter A --> char(97)
--and increasing. the offset is the how may we are in the loop
--times the number of bytes we are putting in the string, and again
--the length of the data we are writing
SET value.write(replicate(char(97 + @offset),1000),@offset*1000, 1000)
WHERE testBIGTextId = 1

SET @offset = @offset + 1
END
go
select testBIGtextId, len(value) as CharLength
from testBIGtext
go
Dave Edelman
Firstly, bear with me - I am a self-taught T-SQL user coming from a software background :-)

The field itself is an IMAGE field, I am merely using the functions to allow me to

a) get a pointer to the right field and the right position in that field &
b) insert a bunch of characters into the given position

If I have misunderstood what you mean by "just change your text field", then could you explain how I "import it that way" with regards varchar or varbinary data

Thanks for your time,
Paul
Tanvir Huda
TEXT is going away in favor of varchar(max) or varbinary(max) which allows you to define a field up to 2gbs in size.

Just change your text field to a varchar(max) or varbinary(max) and import it that way.
VERY large binary import/export headache

Answer this question