Is there a SQL query I can use to migrate data from an "Image" column to a "FileStream" column?

Question

My SQL Express database has run out of room so I'm migrating my heavy data from the old "image" blob columns to the new (varbinary) "filestream" columns in SQL Server 2008.

I was about to write an application to do it, but I thought there may be a clever way to do it in SQL that I hadn't thought of.

Does anyone know of a way to achieve this in a simple manner in SQL?

Assume I have the following table:

TABLE: [Data]
COLUMN: ID INT
COLUMN: ImageFile IMAGE
COLUMN: FileStreamFile VARBINARY(MAX) FILESTREAM DEFAULT(0x)

Obviously with the ImageFile being the old column I want to migrate to FileStreamFile

Answer 1

have you tried casting your image to varbinary(max) in the update?

UPDATE [Data]
SET    [FileStreamFile] = CAST([ImageFile] AS VARBINARY(MAX))

Based on this MSDN page, looks like that should work.

Answer 2

Be sure you enable the FILESTREAM feature.

YOu'll want to create a table that supports filestreams, per the code below (from MSDN):

CREATE TABLE Archive.dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
    [SerialNumber] INTEGER UNIQUE,
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

It looks like things are pretty transparent after that--i.e., adding filestreams is handled by SQL Server w/ minimal effort on your part. For eg:

INSERT INTO Archive.dbo.Records
    VALUES (newid (), 3, 
      CAST ('Seismic Data' as varbinary(max)));
GO