Save an image from SQL Server to a file using SQL

Images take up a lot of space in a database. SQL Server databases aren't cheap to come by at a hosting company and especially not big ones. Therefore, the last thing you would want, is to save your images into the database. I've never been in favor of this method, to use the image type in SQL Server. I always recommend saving the path as a VARCHAR in the database.

Yet, sometimes, you're stuck with someone else's database design and all of a sudden the issue of space comes up and there you are in the middle of a struggle to save the images from SQL Server to a file using SQL. Sure, you could use other programming languages, but I couldn't resist using SQL :).

This script will help with this task:

DECLARE @SOURCEPATH VARBINARY(MAX),
@DESTPATH VARCHAR(MAX),
@ObjectToken INT,
@image_ID BIGINT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT Image_Data, image_ID from T_MyImageTable
OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @DESTPATH = 'c:mypath' + CAST(@image_ID AS varchar) + '.jpg'

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @SOURCEPATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @DESTPATH, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID
END

CLOSE IMGPATH
DEALLOCATE IMGPATH

Note: If your image table is very large then this process will require a lot of resources from your computer. In that case, you might consider using a where clause in the cursor select statement.
For example: SELECT Image_Data, image_ID from T_MyImageTable WHERE image_ID < 10000
Then he will first extract the first possible 10000 images. Next, run the script again with
SELECT Image_Data, image_ID from T_MyImageTable WHERE image_ID >= 10000 AND image_ID &lt; 20000
and so on ...

2017-01-01 23:36:31


< Back