Import files into SQL Server 2008 database

I'm trying to import a large amount of files from one database into another. The original database stored the filenames in a database table and the actual files somewhere on disk.

The new database uses filestream to store the files.

I have imported files into the new database previously using OPENROWSET, but in those cases I provided the full path manually which worked fine.

As in this case there are multiple files I figured I could dynamically build the path combining the location of the directory on the disk with the filenames from the old DB.

However, as it turns out, OPENROWSET doesn't allow for concatenating strings. I found that the way to go would be to use dynamic SQL (http://stack开发者_运维百科overflow.com/questions/6621579/t-sql-issue-with-string-concat).

However, I don't know how to combine the information from the old database table with OPENROWSET to be able to insert the files into the new database.

My current idea is something like this:

create table #Files
(
    IssueId int not null,
    FileName nvarchar(12) not null,
    FullPath nvarchar(255) not null
)

insert into #Files
select IssueId, FileName, @FilePath + '\' + FileName
from OLDDBO.dbo.Files

In this way, I have the ID, filename and full path. But how do I insert these three items together with the binary data into the new DB? I hope someone can help

When they say dynamic, they mean that you have to contruct the SQL, and then execute it. So, using your example, you will need to do something like this:

declare @sql varchar(max)

set @sql = 'insert into #Files  
select IssueId, FileName, @FilePath + '\' + FileName, 
CAST(fileStreamField AS VARBINARY(MAX))       
FROM OPENROWSET(BULK '' + @FilePath + '\' + FileName + '',SINGLE_BLOB)
from OLDDBO.dbo.Files'

execute(@sql) 

So what you are basically doing is constructing the sql to insert, and then executing it. Note the double '' before the filepath - that is there so your output statement eventually has the single quote.

The syntax is untested, so let me know if it works out...

PS. you will need to add a new field for the fileStream in #Files