Post reply

Using Variables with openrowset bulk

  • September 19, 2007 at 3:05 pm

    #180996

    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! 

     

  • September 20, 2007 at 7:59 am

    #734940

    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?

  • September 20, 2007 at 8:16 am

    #734950

    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