Barry Doyle Barry Doyle - 3 months ago 19
SQL Question

SQL Server Management Studio Save a PNG to a Field

I have a database that I'm accessing via SQL Management Studio 2014 (SMS2014). There is a field

Signature
on my table
User
that its a
varbinary(max)
type.

I have a folder of PNG images that I'd like to manually add to each record in the
User
table to file the
Signature
field.

How can I accomplish this? Is there an easy way I can do it using SMS2014 or another way maybe using a query that I run with different values for each record I want to set?

Answer

use the below script..

 UPDATE Yourtable
  SET Signature  = BulkColumn from Openrowset( Bulk 'C:\Images\image.PNG', Single_Blob) as UserImage 
 WHERE userID=@userID --mention the user ID

If you wanted to loop through the images.try something like below. Append the user id's of your User table with the image name (like image1,image2) and so taht we can easily identify which image belongs to which user..

DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)


SET @count = 1

WHILE @count< 101 --total count of images

BEGIN

SET @imgString = 'C:\images\Image' + CONVERT(varchar,@count) + '.png'

SET @insertString = N'UPDATE User
                      SET Signature= BulkColumn
                      FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as UserImage
                      where userID = @count'

EXEC(@insertString)

SET @count = @count + 1

END

GO