Insert into SQL Server with variable

Question

I am using Microsoft Sql Server 2008. I am using a view (dbo.building_piclink) to get photo names (@imgName) 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 att_name but I can't figure out the syntax on the insert statement to add it in.

DECLARE @imgString varchar(800)
DECLARE @insertString varchar(3000)
DECLARE @imgNumber int
Declare @imgName varchar(100)

SET @imgNumber = 1

WHILE @imgNumber <> 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

I have tried

SET @insertString = 'INSERT INTO dbo._building__ATTACH (DATA, ATTNAME)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg,' + @imgName

but I get an error like this:

Msg 208, Level 16, State 1, Line 1 Invalid object name 'b26382_3_775682.jpg'

I have tried just doing the insert on the att_name:

SET @insertString = 'INSERT INTO dbo._buildingpoint__ATTACH (ATT_NAME)' + @imgName

with no luck. I am missing something in the syntax.

Thank you!

Answer 1

Well, yes, your insert string is not correct - right now, you get something like:

INSERT INTO dbo._building__ATTACH (DATA, ATTNAME)
   SELECT * 
   FROM OPENROWSET(BULK N'b26382_3_775682.jpg', SINGLE_BLOB) as tempImg, b26382_3_775682.jpg

while if you want to insert the file name as a string, you need to put it into single quotes (and I would also reverse the order of the column in the INSERT):

INSERT INTO dbo._building__ATTACH (ATTNAME, DATA)
   SELECT 
      'b26382_3_775682.jpg', tempImg.* 
   FROM 
      OPENROWSET(BULK N'b26382_3_775682.jpg', SINGLE_BLOB) as tempImg 

Also, as Jon of All Trades suggested : can you try to run just this INSERT statement in isolation, just to see if that statement on its own works correctly? Once that works - then integrate it into your stored proc and turn it into a dynamic SQL statement...

Update: yes - as I said in my answer (a few lines up from here): you need to put the filename that you want to insert into attName into single quotes - your latest comments still doesn't do that....

Try this:

SET @insertString = 
    'INSERT INTO dbo._building__ATTACH (ATTNAME, DATA) ' + 
    'SELECT ''' + @imgName + ''', tempImg.* ' + 
    'FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'

Does this work?