3

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

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

I know that makes no sense because I don't have a where clause, but what would I put in it's place??

Corey Blair
  • 469
  • 1
  • 7
  • 13
  • So you have the path in one table and your question is you want to use that path and take the file and load that file into a column which is present in another table.Do I make sense? – Teja Mar 29 '12 at 19:18
  • Yes I have the file paths in one field and I want to load the file into another field then eventually delete the first field. Switching from images stored in the filesystem to stored in a varbinary(max) field. This is on SQL Server 2008 R2 – Corey Blair Mar 30 '12 at 14:55

2 Answers2

3

You may need to create a stored procedure to do this. I've used the procs outlined in here to good effect:

Reading and Writing Files in SQL Server using T-SQL

My purposes only included text, but binary is mentioned.

Depending upon your version of SQL Server, you might try openrowset, or as @Jeremy Pridemore mentions, you may be able to use the CLR.

UPDATE

This code may help you if you're using SQL2005 or above:

declare @MyFile varbinary(max)
select @MyFile = bulkcolumn 
from openrowset (bulk 'C:\SomeFolder\SomeImage.jpg', single_blob) myfile
select @MyFile
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
1

SQL is not well suited to accessing the file system, but this task would not be difficult to accomplish in a C#/VB.NET program.

EDIT: Tim Lehner's OPENROWSET solution is ideal if you are on SQL 2005 or later.

Chris Kelly
  • 557
  • 2
  • 10
  • SQLCLR could be used here. I don't have time to look up the all the pieces to get it working myself. – Jeremy Pridemore Mar 29 '12 at 20:23
  • @Jeremy Pridemore, you could look [here](http://www.mssqltips.com/sqlservertip/2349/read-and-write-binary-files-with-the-sql-server-clr/). – Tim Lehner Mar 29 '12 at 20:32
  • Thanks! I ended up just writing a small C# application that reads the urls from the database and does a select into query. Doing 100,000+ updates vs 100,000+ inserts was WAY slower. – Corey Blair Apr 13 '12 at 22:13