SQLServer 批量导入目录文件

openrowset的用法可以参考msdn

http://technet.microsoft.com/zh-cn/library/ms190312.aspx

如果文件很多，建议还是用程序倒入了


复制代码 代码如下:

if (object_id (‘t_bulkresult’ ) is not null )

drop table t_bulkresult

create table t_bulkresult (name varchar (1000 ), data image )

go

declare @d varchar (1000 )

set @d = ‘c:\test\’

create table #tb (fname varchar (1000 ), d int , f int )

insert into #tb exec xp_dirtree @d , 1 , 1

delete from #tb where f <> 1

declare @ sql nvarchar (max )

select @ sql = isnull (@ sql , ” )+ ‘insert into t_bulkresult select ”’ +@d + fname + ”’,* from openrowset(bulk n”’ +@d + fname + ”’, single_blob) b

‘

from #tb a

exec sp_executesql @ sql

drop table #tb

select * from t_bulkresult