Monday 12 December 2011

Insert and Update image field in SQL Server 2008/2005

Insert and Update image field in SQL Server 2008/2005



This article is about how to insert and update image or varbinary field from within SQL Server itself.

Before we move further, If you wish to look at the 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