| « Cool search utility ... | Microsoft Developer Blog is open now » |
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:\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)".
1 comment
thanks for your good suggested method for insert picture in SQL with bulk copy . I usage it .
Can you help me for Export Image from table into file with bulk tecnology ?
In programmable Language I can't use bcp .
thank for your help .
my e_mail : z_fatah78@yahoo.com
This post has 11 feedbacks awaiting moderation...