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

Question

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.

Answer 1

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