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

14 Responses to “Insert and Update image field in SQL Server 2008/2005”

  1. siva Says:

    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.

  2. siva Says:

    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.

  3. Ritesh Shah Says:

    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.

  4. Ritesh Shah Says:

    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.

  5. thomas Says:

    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

  6. thomas Says:

    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

  7. thomas Says:

    Hello sir,After saving excel file into database , how can retrieve the same file. Please help me….its urgentregardsthomas antony

  8. thomas Says:

    Hello sir,After saving excel file into database , how can retrieve the same file. Please help me….its urgentregardsthomas antony

  9. Ritesh Shah Says:

    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

  10. Ritesh Shah Says:

    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

  11. Rachel Says:

    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?

  12. Rachel Says:

    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?

  13. Ritesh Shah Says:

    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.

  14. Ritesh Shah Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: