Insert and Update image field in SQL Server 2008/2005

Few months back I have written one article which was showing how to store image and other files into SQL Server column by converting it to byte object and receive it back. That was with the help of C# but now this time I am going to show, how you can insert and update image or varbinary field from within SQL Server itself.
Before we move further, If you wish to look at my previous article with C# script, please have a look at:
Ok, now let us move ahead with our script in SQL Server itself.
–create table for demonstration
create table emps
(
      name varchar(50),
      dept varchar(10),
      empImg image
)
GO


–insert statement with single_blob to upload image to SQL Server
INSERT INTO emps ([Name],dept,empImg)
SELECT ‘Ritesh’,‘MIS’,
(select * FROM OPENROWSET(BULK ‘C:\Ritesh.JPG’, SINGLE_BLOB) AS img)
GO


–check the inserted data
select * from emps
GO

–update your table, along with image also.
update emps
set empImg=(select * FROM OPENROWSET(BULK ‘C:\Ritesh1.JPG’, SINGLE_BLOB) AS img), dept=‘IT’
where name=‘Ritesh’
GO

–check the data whether it has been updated
select * from emps
go

 
Happy SQLing!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements