How do I update a varbinary (max) column with an outline stored in a varchar column?

I currently have a varchar (255) column that stores the path to a small image file. I want to try and load a file into a new column of type varbinary (max), but don't know how to write a stored procedure to do this. Something like that

UPDATE MyTable
SET image = "file located in field imagePath"

      

I know this is pointless because I don't have a where clause, but what would I put a place in it?

+3


source to share


2 answers


SQL is not suitable for accessing the filesystem, but this task is not difficult in a C # / VB.NET program.



EDIT: Lechner's type solution OPENROWSET is ideal if you are on SQL 2005 or newer.

+1


source


You may need to create a stored procedure for this. I used the procs described here: "//" →

Reading and Writing Files in SQL Server Using T-SQL

My targets only included text, but the binary is mentioned.

Depending on your version of SQL Server, you can try openrowset or, as @Jeremy Pridemore mentions, you can use the CLR .



UPDATE

This code may help you if you are using SQL2005 or higher:

declare @MyFile varbinary(max)
select @MyFile = bulkcolumn 
from openrowset (bulk 'C:\SomeFolder\SomeImage.jpg', single_blob) myfile
select @MyFile

      

+3


source







All Articles