How to insert a blob into a database using sql server management studio

Question

How can I easily insert a blob into a varbinary(MAX) field?

As an example:

thing I want to insert is: c:picture.png
the table is mytable
the column is mypictureblob
the place is recid=1

Answer 1

You can insert into a varbinary(max) field using T-SQL within SQL Server Management Studio and in particular using the OPENROWSET commmand.

For example:

INSERT Production.ProductPhoto 
(
    ThumbnailPhoto, 
    ThumbnailPhotoFilePath, 
    LargePhoto, 
    LargePhotoFilePath
)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET 
    (BULK 'c:imagesricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

Take a look at the following documentation for a good example/walkthrough

Working With Large Value Types

Note that the file path in this case is relative to the targeted SQL server and not your client running this command.