Loading images into SQL Server Using Management Studio

20 February 2010 (0)

If you are storing images in a SQL Server database you can use the following statement to insert data into a BLOB field (e.g. varbinary(max) ) of a table (Sql Server 2005+):

Assuming a table called AdvertImage with an identity col ("ID") a foreign key column ("FK_Advert") and a varbinary(max) col "JPEGImageData"; you could use the following SQL to insert a new row with the image "C:\motoimage\1.1.JPG" being loaded into the JPEGImageData and setting FK_Advert to 12345:

INSERT AdvertImage
(
    FK_Advert,
    JPEGImageData
)
SELECT 12345, JPEGImageData.*
FROM OPENROWSET
    (BULK 'C:\motoimage\1.1.JPG', SINGLE_BLOB) JPEGImageData

MSDN Link

 

Sidenote: everyone seems to have a different opinion on storing images (BLOBs) in a database, unfortunately a lot of the time the opinions are written in stone "never store images in the database, always just store the filename to the image on disk", etc. Like any problem you should weight up all the factors such as image size, read vs write volume, will be using a web farm (if so will the images stored on web server need replicating every time one is inserted or changed), how many users, criticality of db image references (e.g. medical images, etc)

There is a good MSDN article covering choice of BLOB types and considerations.

SHARE:

Add comment

Loading