Using Variables with openrowset bulk
-
I'm trying to insert image files into a table in SQL Server 2005. The T-SQL I used works without variables, but when I introduce a variable into the BULK statement, I get errors.
The table is
CREATE TABLE [Comp].[Photo](
[ID] [int] NOT NULL,
[Photo1] [varbinary](max) NULL,
CONSTRAINT [PK_Photo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
The record ID is 673388
The image filename is the record ID with "_1" on the end: C:\Program Files\SComp\Photos\673388_1.jpg
Why won't the following syntax work?
declare @ID int, @file varchar(max)
set @ID = 673388
Insert Comp.Photo (ID, Photo1)
Select @ID, BulkColumn from Openrowset( Bulk 'C:\Program Files\SComp\Photos\' + convert(varchar(10), @ID) + '_1.jpg', Single_Blob) as Photo1
Thanks, if you can help!
-
You're trying to sneak some dynamic SQL in. You'd need to build your entire INSERT statement, openrowset and all, in a string variable, and then use the
EXEC(@var)
command to run that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thanks, Matt. I'm testing it right now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply