Insert and Update image field in SQL Server 2008/2005

Monday, August 10, 2009 |


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

8 comments:

siva said...

Can you please let me know how to write sql script to store a word document as blob using base64 encoding in the oracle databse.I need to insert few documents at one shot as a part of initializing my application using this script.

Ritesh Shah said...

Hello Siva,

I am sorry, I will not be able to assist you as you are using Oracle and I am Microsoft SQL Server professional.

thomas said...

After saving file into a image file,
how to display the the binary data by using vb6. The file may be XLS or DOC Or Jpg File. After saving, how we can read that file

thomas said...

Hello sir,

After saving excel file into database , how can retrieve the same file. Please help me....its urgent

regards
thomas antony

Ritesh Shah said...

Hello Thomas,

There is no difference in saving Excel file and get it back. all you have to do it is, convert in binary format, save it in database and when you get it back from database, you have to save it on disc with proper extension, if it is excel than "XLS" or "XLSX".

you can save file extension or file's full name with extension in database too so that you can get the same file name with extension

Rachel said...

Hi,
I have numerous word documents that I would like to insert/store into sql server - is there a way of doing this? The documents have 100+ pages and only contain text. I am very new to SQL so I would need the full code with explanations if possible?

Ritesh Shah said...

Hi Rachel,

You can use the same code which I had for the image the only change is, I had used Image datatype for field and you have to use VarBinary(Max) datatype for doc or docx file.

HareKrsna said...

How to pass path Dynamically???