Bulk Loading Images in SQL Server

Loading images and any binary files (like Adobe PDF documents) to a database is not always the best option but sometimes needed. In SQL Server 2005 this process is simplified a lot with the BULK option of OPENROWSET. Here is an example of inserting image data into a VARBINARY(MAX) column (the same applies to loading an Adobe PDF file or any other binary file):

CREATE TABLE Foobar (
  image_data VARBINARY(MAX));
 
INSERT INTO Foobar (image_data)
SELECT image_data
FROM OPENROWSET(
         BULK N'C:\image.jpg',
         SINGLE_BLOB) AS ImageSource(image_data);

Note the use of the SINGLE_BLOB option.

Labels: , ,