title: How do I use a path stored in table with OPENROWSET?
body: <p>I have just recently began using <code>OPENROWSET</code> to insert images into a table. Previously, I would specify the path to each image (1 image = 1 <code>INSERT</code> statement), and use PHP to generate the image's binary string:</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
  VALUES (
      (
          SELECT *
          FROM OPENROWSET(BULK '" . $this-&gt;image['path'] . "', SINGLE_BLOB) AS Binary
      ),
      '" . $this-&gt;image['mime_type'] . "',
      '" . $this-&gt;image['seo_filename'] . "',
      '" . $this-&gt;image['alt'] . "',
      '',
      0
  )
  </code></pre>
  
  <p>However, I am trying to insert all images with a single query. So, I have began storing the path to each image into a table, and now I need to insert each one as I did before (just using the table's path field instead of a PHP string). But, when I attempt the following:</p>
  
  <pre><code>INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
  SELECT
      (
          SELECT *
          FROM OPENROWSET(BULK ImagePath, SINGLE_BLOB) AS Binary
      ),
      MimeType,
      Name,
      Alt,
      '',
      0
  FROM nopRMS..Product_Image_Mappings
  </code></pre>
  
  <p>I receive the following error:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 5<br>
    Incorrect syntax near 'ImagePath'.</p>
  </blockquote>
  
  <p>So, I tried adding quotes around the column's name (to no avail):</p>
  
  <pre><code>INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
  SELECT
      (
          SELECT *
          FROM OPENROWSET(BULK 'ImagePath', SINGLE_BLOB) AS Binary
      ),
      MimeType,
      Name,
      Alt,
      '',
      0
  FROM nopRMS..Product_Image_Mappings
  </code></pre>
  
  <blockquote>
    <p>Msg 4860, Level 16, State 1, Line 1<br>
    Cannot bulk load. The file "ImagePath" does not exist.</p>
  </blockquote>
  
  <p>There has to be a way to accomplish this, I just cannot find the proper syntax online anywhere. Does anyone know how to tell SQL Server to get the path (string) from <code>dbo.Product_Image_Mappings.ImagePath</code>?</p>
  
  <p><strong>UPDATE</strong><br>
  I forgot to give you an example of a value that <code>dbo.Product_Image_Mappings.ImagePath</code> would return. It's paths like <code>\\DEREK\WebImages\1\ca-82300.jpg</code>...</p>
  
  <p><strong>UPDATE</strong><br>
  It appears that Eirikur Eiriksson has provided a solution in <a href="http://www.sqlservercentral.com/Forums/Topic1570821-3077-1.aspx" rel="nofollow">this thread</a>, but this looks like an overly-complicated method of achieving the same end...</p>
  
  <p><strong>UPDATE (Attempt Using Eirikur Eiriksson's Method)</strong></p>
  
  <pre><code>DECLARE @SQL_STR NVARCHAR(MAX) = N'';
  
  SELECT @SQL_STR = STUFF(
      (
          SELECT 
              N'
              UNION ALL
              SELECT '
              + N'(SELECT X.BulkColumn FROM OPENROWSET(BULK '
              + NCHAR(39) + im.ImagePath + NCHAR(39)
              + N', SINGLE_BLOB) AS X) AS PictureBinary,'
              + NCHAR(39) + im.MimeType + NCHAR(39)
              + N' AS MimeType,'
              + NCHAR(39) + im.Name + NCHAR(39)
              + N' AS SeoFilename,'
              + NCHAR(39) + REPLACE(im.Alt, '''', '''''') + NCHAR(39)
              + N' AS AltAttribute,'
              + N'NULL AS TitleAttribute,'
              + N'0 AS IsNew'
          FROM nopRMS..Product_Image_Mappings im
          FOR XML PATH(''), TYPE
      ).value('.[1]','NVARCHAR(MAX)'),1,12,N''
  )
  
  INSERT INTO nopCommerce..Picture (PictureBinary, MimeType, SeoFilename, AltAttribute, TitleAttribute, IsNew)
  EXEC (@SQL_STR);
  </code></pre>
  
  <p>This kinda worked, but it only inserted 42 rows (out of 7200+)... I need this to be 100% accurate :( I admit though, I may need to change something about this query, but I don't know anything about it (aside from the basic INSERT, SELECT, etc.)</p>