# How do I load them as varbinary(MAX) format into SQL Database?

There are 1000 picture that are jpeg format.How do I load them as varbinary(MAX) format into SQL Database?But at the same time I want the bulk insert functionality.

-
Is it possible for you to store the JPEG in a file system and only store the path to the JPEG in SQL Server? –  Simon Hughes Sep 16 '11 at 10:52
@Simon Hughes: See dba.stackexchange.com/q/2445/630 for that question. IMO, that is a bad idea –  gbn Sep 16 '11 at 11:17

Yes, use OPENROWSET with BULK. You need a format file though.

Assuming you want to attach blobs to existing records, something like:

INSERT SomeTable (id, blob)
select
X.SomeID, B.Blob
from
SomeKeyTable X
JOIN
OPENROWSET (
BULK 'c:\myfile.txt',
FORMATFILE = 'c:\myfileformat.txt'
) B ON X.AKey  = B.AKey

-
is it possible to save multiple images which are in the c:\myfile.txt as embedded binary data to the SomeTable. We have a requirement for data migration from derby to SQL Server. The images in derby are read as binary and embedded in the txt file, obviously the images are converted into some gibberish format in the text. There needs to be a delimiter for the different images. I tried using your example with various combinations in the bcp format but with no success. Can you please help me with that. Thanks –  user8681 May 9 '12 at 22:43
@Tatha: You'd have to write a utility to do the import/export. Or use SSIS. I don't think my method will work for you, sorry –  gbn May 10 '12 at 6:48