是否有可用于将数据从“Image”列迁移到“FileStream”列的SQL查询？

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.

我的SQL Express数据库已经用完了空间,因此我将大量数据从旧的“image”blob列迁移到SQL Server 2008中的新(varbinary)“filestream”列。

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.

我正准备编写一个应用程序来执行此操作,但我认为在SQL中可能有一种我没想过的聪明方法。

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

有没有人知道在SQL中以简单的方式实现这一目的的方法?

Assume I have the following table:

假设我有下表:

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

表:[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

显然,ImageFile是旧列,我想迁移到FileStreamFile

2 个解决方案

#1

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

你有没有尝试在更新中将图像转换为varbinary(max)?

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

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

基于此MSDN页面,看起来应该工作。

#2

Be sure you enable the FILESTREAM feature.

确保启用FILESTREAM功能。

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

你想根据下面的代码(来自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:

之后事情看起来非常透明 - 即,添加文件流由SQL Server处理,您只需要很少的努力。例如:

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

#1