In the dark days there was a simple exe for uploading files into a database; that has gone. Found myself working on a data import and need to upload my files into blob;s openquery is my friend. You need to enable cmd shell or find another way of getting the file names into SQL Server but this will let you pick up all the files in a folder in one hit. Its quick too 10k RTF / Word Files on my test server took about 2 minutes.

go

drop table dbo.FilesInFolder

drop table myTable2

Create Table dbo.FilesInFolder(myFileName nvarchar(255))

Create TABLE myTable2(Document image, myFileName nvarchar(255))

Insert into FilesInFolder

exec master..xp_cmdshell ‘for %a in (C:\Users\Administrator\Desktop\F01\*.*) do @echo %a’

exec master..xp_cmdshell ‘for %a in (C:\Users\Administrator\Desktop\Excel\*.*) do @echo %a’

Alter table FilesInFolder add FileNumber int identity(1,1)

Declare @i int, @maxi int, @FileName nvarchar(1000), @mYSQL nvarchar(4000)

Select @i = 1, @maxi = COUNT(*) from FilesInFolder where not myFileName is null

While @i <= @maxi

Begin

select @FileName = myfilename from FilesInFolder where filenumber = @i

select @mYSQL = ‘INSERT INTO myTable2(Document, myfilename) SELECT *, ”’ + REPLACE(@FileName,’C:\Users\Administrator\Desktop\F01',”) + ”’ FROM OPENROWSET(BULK N”’ + @FileName + ”’, SINGLE_BLOB) as i’

exec sp_executesql @mYSQL

select @i = @i + 1

End