Results 1 to 5 of 5

Thread: Bulk for images

  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: Bulk for images

    Hello,
    i have to load some images in a table from png files.
    I found something like this:

    Code:
    UPDATE people
    SET image = (SELECT BulkColumn FROM OPENROWSET (BULK 'c:\images\0001.png' , SINGLE_BLOB) AS x)
    My trouble is that the file name must changes with the id field, for example id 0001 has 'c:\images\0001.png', id 0002 has 'c:\images\0002.png'.
    How can I change the bulk filename with a field of "people" table?

    Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could use xp_cmdshell to get a list of files from the directory and then execute dynamic SQL to load each one by name.
    You could also set up a DTS or SSIS package with variables to represent filenames and program it to load your data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2009
    Posts
    10
    Ok, but suppose that i have the filename in a "people" field, how can i use it into the sub-select?

  4. #4
    Join Date
    Nov 2009
    Posts
    10
    No way to do something like this?
    Code:
    UPDATE people
    SET image = (SELECT BulkColumn FROM OPENROWSET (BULK 'c:\images\' + people.id +'.png' , SINGLE_BLOB) AS x)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, with dynamic SQL. You create your statements as strings and then use EXEC to run them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •