Every once in a while I find myself needing to load an image file into a database field.

Assume there is a table similar to this and I already have one or more widget records.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
create table dbo.Widget (
  WidgetId tinyint not null primary key,
  WidgetName varchar(40) not null,
  WidgetDrawing varbinary(max) null
);

insert into dbo.Widget (WidgetId, WidgetName) 
values 
(1, 'Widget A'),
(2, 'Widget B'),
(3, 'Widget C');

Now I have a drawing of one of those widgets which I would like to load into the WidgetDrawing column.

1
2
3
4
5
6
update dbo.Widget
set WidgetDrawing = (
  select BulkColumn 
  from openrowset(bulk 'c:\temp\widget-a.png', single_blob) as img
  )
where WidgetId = 1;