title: Import files into SQL Server 2008 database
body: <p>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.</p>
  
  <p>The new database uses filestream to store the files. </p>
  
  <p>I have imported files into the new database previously using OPENROWSET, but in those cases I provided the full path manually which worked fine.</p>
  
  <p>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.</p>
  
  <p>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://stackoverflow.com/questions/6621579/t-sql-issue-with-string-concat).</p>
  
  <p>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.</p>
  
  <p>My current idea is something like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>