Software And Tools - Microsoft Developer

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:\Blue Lace 16.bmp', Single_Blob) as EmployeePicture

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