SQL Tips by Namwar Rizvi

June 13, 2007

Saving Images in a SQL database using SQL

Filed under: Information,New Features,Query,SQL Server 2005,tips,TSQL — namwar @ 7:37 PM

Ever wondered how you can load an image in table by using TSQL? Before SQL Server 2005, it was not easy enough to load a file in the table. Now in SQL Server 2005, you can use OPENROWSET command to load any type of file in a varbinary column by a single INSERT statement. Following code demonstrates this:

–Create a table to save Image data
Create table tblImages
(
ImageId int,
ImageData varbinary(max)

)

— Perform INSERT to Save an image
Insert into tblImages
(ImageId,ImageData)
SELECT 1, BulkColumn
FROM OPENROWSET(BULK N‘SamplePicture.jpg’, SINGLE_BLOB) AS Document

–Display table data after INSERT
Select * from tblImages

Advertisements

1 Comment »

  1. and what is the procedure to retrieve the image again using t-sql?

    Comment by manpeet — April 6, 2009 @ 10:07 AM | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: