Sunday, October 30, 2005

Saving images as BLOB into SQL Server 2005

In this article we would look into the easiest way of importing an image as BLOB content into a SQL table.

1. Openrowset has new bulk features introduced in SQL Server 2005.

2. Openrowset supports bulk operations through a built-in bulk provider that allows data from a file to be read and returned as a rowset.

3. Using the BULK rowset provider you can load a file into a table's column using regular DML.

4. Unlike SQL Server 2000, instead of being limited to Text, NText and Image datatypes for large objects, in SQL Server 2005 we can also use Varchar(max), nvarchar(max) and Varbinary(max) datatypes. The new MAX option allows you to manipulate large objects the same way you manipulate regular datatypes

5. With OPENROWSET you'll be able to return a rowset from a file as a single varbinary(max), varchar(max) or nvarchar(max) data type value. We'll use "SINGLE_BLOB", "SINGLE_CLOB" or "SINGLE_NCLOB" to diffentiate what kind of single-row, single-column data is being read.

Sample table structure:


CREATE TABLE EmployeeProfile
(
EmpId INT,
EmpName VARCHAR(50) not null,
EmpPhoto VARBINARY(MAX) not null
)
GO


Import image into this table:

Openrowset with the Bulk option requires a correlation name (also known as a range variable or alias) in the FROM clause.


INSERT EmployeeProfile (EmpId, EmpName, EmpPhoto)
SELECT 1001, 'Vadivel', BulkColumn
FROM OPENROWSET( BULK 'C:\Sample.gif', Single_Blob) AS EmployeePicture
GO


I suggest to read http://msdn2.microsoft.com/en-us/library/ms175915.aspx to know more about "BCP, Bulk insert, and Openrowset (Bulk)".

6 comments:

Param said...

Suppose if i want to upload the image to a database through my application and my application contains the binary data of an image.Can binary data be inserted into the database using Openrowset?

Anonymous said...

Thanks for posting this - really helped me.

Anonymous said...

Same here. I was searching exactly the same info like this. Thanks.

Anonymous said...

How to do same in sql server 2000, suppose I have file names along with file id, in a table and on certain condition i select few files and file id. now want to insert file id and image data in a different table.
Thanks in advance.

ran said...

same am also searching for something like this.... thanks from chennai

Aaron said...

GOLD!!!!!!!