How to load a photo file to SQL Server 2005 database

In SQL Server 2000 or SQL Server 7, users had to use client programs like BII from SQL Server 2000 Resource Kit or custom ADO programs to load images to a SQL Server database.

In SQL Server 2005 it is possible to use OPENROWSET() function with new BULK OLE DB provider to read a file and load it as a rowset. It can then be assigned to varbinary(max) column in a single Update statement:

CREATE TABLE dbo.Equipment(
	EqId int NOT NULL,
	Make varchar(50) NOT NULL,
	Model varchar(50) NOT NULL,
	EqTypeId smallint NULL,
	EqDesc nvarchar(max) NULL,
	EqImage varbinary(MAX) NULL
) ON PRIMARY
GO
INSERT INTO dbo.Equipment (EqId,Make,Model,
                           EqTypeId,EqDesc)
VALUES (1, 'TB', 'DT 2005', 
	1 ,'desktop')

UPDATE dbo.Equipment
SET EqImage = (SELECT * 
               FROM OPENROWSET(BULK 'C:\My Pictures\desktop.bmp', 
                               SINGLE_BLOB) AS a )
WHERE EqID = 1