在 SQL Server 2005 中导入图像 [英] Importing images in SQL Server 2005

问题描述

亲爱的，我还有一个问题.我在 SQL 表雇员中有.这张桌子有很多细节，但我缺少图像或照片.

Dear All, I have another issue. I have in the SQL table Employees. This table has lot of details but I was missing images or photos.

所以我设法为所有员工拍了所有照片，但我把照片放在文件夹里.每张图片都像 Employee_id 一样命名，它与表中的记录相匹配.如何将图片导入到 SQL Employee 表中以将图片名称与 Employee_id 匹配.

So i managed to take all pictures for all employees but I have the pictures in the folder. Each picture is named like an Employee_id which matches the record in the table. How do I import images into SQL Employee table to match the name of the picture to Employee_id.

有什么想法吗?

推荐答案

游标和一点动态 SQL 应该可以解决问题.

A cursor and a bit of dynamic SQL should do the trick.

declare EmployeeCursor cursor fast_forward for
    select Employee_id
        from Employee

declare @sql nvarchar(4000)
declare @Employee_id int

open EmployeeCursor      

while (1=1) begin
    fetch next from EmployeeCursor into @Employee_id

    if @@FETCH_STATUS<>0 break

    set @sql = N'UPDATE Employee
                     SET ImageColumn = 
                         (SELECT * FROM 
                              OPENROWSET(BULK N''c:\images\' + cast(@Employee_id as nvarchar(10)) + N'.jpg'', SINGLE_BLOB) AS img)
                              WHERE Employee_id = ' + cast(@Employee_id as nvarchar(10))

    exec(@sql)   
end /* while */

close EmployeeCursor
deallocate EmployeeCursor

这篇关于在 SQL Server 2005 中导入图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！