title: Insert into SQL Server with variable
body: <p>I am using Microsoft Sql Server 2008. I am using a view (<code>dbo.building_piclink</code>) to get photo names (<code>@imgName</code>) and insert the photos into a table from a file. I need to also take this photo name and add it into a column called <code>att_name</code> but I can't figure out the syntax on the insert statement to add it in. </p>
  
  <pre><code>DECLARE @imgString varchar(800)
  DECLARE @insertString varchar(3000)
  DECLARE @imgNumber int
  Declare @imgName varchar(100)
  
  SET @imgNumber = 1
  
  WHILE @imgNumber &lt;&gt; 10
  
  BEGIN
  
  SELECT @imgName = Lower(items) FROM dbo.building_piclink
  
  SET @imgString = 'C:\Documents and Settings\Administrator\Desktop\photos\' + @imgName
  
  
  SET @insertString = 'INSERT INTO dbo._building__ATTACH (DATA)
  SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
  
  Print @insertString
  
  SET @imgNumber = @imgNumber + 1
  
  EXEC(@insertString)   
  
  END
  
  GO
  </code></pre>
  
  <p>I have tried </p>
  
  <pre><code>SET @insertString = 'INSERT INTO dbo._building__ATTACH (DATA, ATTNAME)
  SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg,' + @imgName
  </code></pre>
  
  <p>but I get an error like this:</p>
  
  <blockquote>
    <p>Msg 208, Level 16, State 1, Line 1 Invalid object name
    'b26382_3_775682.jpg'</p>
  </blockquote>
  
  <p>I have tried just doing the insert on the <code>att_name</code>:</p>
  
  <pre><code>SET @insertString = 'INSERT INTO dbo._buildingpoint__ATTACH (ATT_NAME)' + @imgName
  </code></pre>
  
  <p>with no luck. I am missing something in the syntax.</p>
  
  <p>Thank you!</p>