2007-05-24 15:04 | 只看楼主
树型| 收藏| 小 中 大 1

SQLServer2005里载入多个文件到VarBinary 列

SQLServer2005里载入多个文件到VarBinary 列

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址

--SQL2000里的方法请点击这里


本文讨论了怎样载入多个图片或者文本文件到SQLServer数据库的varbinary列里。

前提

a 确保xp_cmdshell可用。如果不可用，执行下面的命令

use master
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

b 确保创建可一个有varbinary(max)列的表，因为image在将来的版本里不再被支持。还要确保表里至少有一列用来存储文件名。

create table [myblob] (
[id] int identity(1,1),
[image name] varchar(100),
[blob] varbinary(max))
Go

假定我们要从C:\Windows文件夹里载入BMP文件到数据库test的架构为DBO的表MyBlob里。

步骤1

执行下面的脚本。这在master数据库里创建了一个存储过程usp_uploadfiles以便可以在任何数据库里执行和调用。

USE [master]
GO
IF  EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[usp_uploadfiles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_uploadfiles]
go
set quoted_identifier off
go
create procedure usp_uploadfiles
@databasename varchar(128),
@schemaname varchar(128),
@tablename varchar(128),
@FileNameColumn varchar(128),
@blobcolumn varchar(128),
@path varchar(500),
@filetype varchar(10),
@printorexec varchar(5) = 'print'
as
set nocount on
declare @dircommand varchar(1500)
declare @insertquery varchar(2000)
declare @updatequery varchar(2000)
declare @count int
declare @maxcount int
declare @filename varchar(500)
set @count=1
set @dircommand = 'dir /b '+@path+@filetype
create table #dir (name varchar(1500))
insert #dir(name) exec master..xp_cmdshell @dircommand
delete from #dir where name is NULL
create table #dir2 (id int identity(1,1),name varchar(1500))
insert into #dir2 select name from #dir
--select * from #dir2
set @maxcount = ident_current('#dir2')
while @count <=@maxcount
begin
set @filename =(select name from #dir2 where id = @count)
set @insertquery = 'Insert into ['+@databasename+'].['+@schemaname+'].['+@tablename+']
    ([' +@filenamecolumn +']) values ("'+@filename+'")'
set @updatequery = 'update ['+@databasename+'].['+@schemaname+'].['+@tablename+']   
set ['+@blobcolumn+'] =    (SELECT *    FROM OPENROWSET(BULK "'+@path+@filename+'", SINGLE_BLOB)AS x )
  WHERE ['+@filenamecolumn +']="'+@filename+'"'
if @printorexec ='print'
begin
print @insertqueryprint @updatequery
end
if @printorexec ='exec'
begin
exec (@insertquery)
exec (@updatequery)
end
set @count = @count +1
end
go

该过程有下面的参数：
@databasename = 数据库名
@schemaname = 架构名
@tablename = 表名
@FileNameColumn = 文件列名
@blobcolumn = varbinary(max)列的列名
@path = 路径名。本例中为C:\Windows\
@filetype = 文件类型。本例中为BMP
@printorexec =如果为Print则显示产生的命令；如果为Exec则直接执行命令--这将载入所有的文件

步骤2

通过传递下面的参数执行存储过程。

Exec master..usp_uploadfiles
@databasename ='test',
@schemaname ='dbo',
@tablename ='myblob',
@FileNameColumn ='Image Name',
@blobcolumn = 'blob',
@path = 'c:\windows\',
@filetype ='*.bmp',
@printorexec ='print'

这将产生所有需要为每个文件创建一行并用正确的文件名更新行的命令。

Insert into [test].[dbo].[myblob] ([Image Name]) values ("Blue Lace 16.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Blue Lace 16.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Blue Lace 16.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Coffee Bean.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Coffee Bean.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Coffee Bean.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("FeatherTexture.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\FeatherTexture.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="FeatherTexture.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Gone Fishing.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Gone Fishing.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Gone Fishing.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Greenstone.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Greenstone.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Greenstone.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("prairie Wind.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Prairie Wind.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="prairie Wind.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Rhododendron.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Rhododendron.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Rhododendron.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("River Sumida.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\River Sumida.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="River Sumida.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Santa Fe Stucco.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Santa Fe Stucco.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Santa Fe Stucco.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Soap Bubbles.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Soap Bubbles.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Soap Bubbles.bmp"
Insert into [test].[dbo].[myblob] ([Image Name]) values ("Zapotec.bmp")
update [test].[dbo].[myblob] set [blob] = (SELECT *    FROM OPENROWSET(BULK "c:\windows\Zapotec.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Zapotec.bmp"

步骤3

通过传递下面的参数执行存储过程。

Exec master..usp_uploadfiles
@databasename ='test',
@schemaname ='dbo',
@tablename ='myblob',
@FileNameColumn ='Image Name',
@blobcolumn = 'blob',
@path = 'c:\windows\',
@filetype ='*.bmp',
@printorexec ='exec'

这将基于指定的文件类型为文件夹里的每一个文件创建一行并载入相应的文件。 成功载入后可以通过下面的查询来检查：

Select * from [test].[dbo].[myblob]

上面查询的结果如图1所示：


图1

结论

本文讨论了怎样载入多个图片或文本文件到SQLServer数据库的varbinary列。
最后编辑拓狼 最后编辑于 2007-05-24 15:05:58
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。