Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

up vote 2 down vote accepted

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  
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.