SQL server. How to insert binary data.

[sunaj]

Hi,

Maybe I should post this in the SQL server forum, but since this is where the expertice is...

I've got a LOT of data that I want to insert into binary fields in SQL server. Since the fields will be larger than 8000 bytes, I'm using the 'image' datatype to store the data (but I guess that is is extactly the same as for the varbinary datatype).

What is the best way (=fastest) to that?

- Store the data in an array and use INSERT
- Use ado.stream
- Write a file that contains both text and the binary data and use BULKLOAD to load the data
- Other?

Thanks in advance
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[sunaj]

If anybody should find it interesting.

I found 3 solutions.
1) Print the data to a file using put and then transfer the binary file to the sql server using appendchunk or the stream object. Probably very slow (I didn't try it).
2) use bulk insert with a format file. However the formatfile has to give the lengh of the data, which is not very usefull in my case where I need to load many 'images' of different sizes, but it works.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q197043

3)
Use Copymemory to transfer the data to a string (similar to 1), just leaving out the file):
---------------------------------------------------
Const n As Long = 3000
'Create test data
ReDim v(n - 1)
For i = 0 To n - 1
v(i) = CSng(Rnd * 100)
Next i
'Copy data to string
s = Space(4 * n)
Call CopyMemory(ByVal s, v(0), 4 * n)
'Insert to database
Rst.Fields("data&quot.AppendChunk s
Rst.Update
----------------------------------------------------
to retrieve:
----------------------------------------------------
strData = String(ColSize, 0)
strData = Rst.Fields(0).GetChunk(ColSize)
l = Len(strData)
ReDim Preserve b(l \ 4 - 1)
Call CopyMemory(b(0), ByVal strData, l)
----------------------------------------------------

If anybody can think of a way to improve on this so I don't have to copy the data from the array to the string and visa versa. Please post your solution here. Thanks



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'