SQL Server Management Studio Save a PNG to a Field

Question

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 1

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