SQL：使用文件夹中的图片更新表，按 id #

发布于 2024-12-01 00:59:11 字数 189 浏览 2 评论 0原文

我目前有一个包含列（ id#(int), picture(varbinary(MAX) ）的表，并且我的硬盘上有一个包含大量图片的文件夹。文件夹中的图片由“id”命名我想将它们与我的表相匹配，我该怎么做？

Example:

Table Row: id=25166, picture=NULL
25166.jpg

原文

I have a table at the moment with columns ( id#(int), picture(varbinary(MAX) ), and I have a folder on my HDD with a ton of pictures. The pictures in the folder are named by the 'id' I want to match them with in my table. How can I do this?

Example:

Table Row: id=25166, picture=NULL
25166.jpg

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

原谅过去的我 2024-12-08 00:59:11

如果您需要一个仅使用 SQL checkout 的示例，请执行以下操作：

它使用游标循环遍历不包含图像数据的每一行，并为找到的每一行使用 OPENROWSET BULK 提供程序加载文件。

CREATE TABLE ImageStore
( 
id INT, 
picture VARBINARY(MAX) NULL
)
GO 

INSERT INTO ImageStore (id) VALUES(25166)
INSERT INTO ImageStore (id) VALUES(25167)

DECLARE @id INT
DECLARE image_cursor CURSOR FOR 
SELECT id FROM ImageStore WHERE picture IS NULL

OPEN image_cursor;

FETCH NEXT FROM image_cursor 
INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    DECLARE @imagePath NVARCHAR(255)
    SET @imagePath = 'C:\' + RTRIM(LTRIM(STR(@id))) + '.gif'

    SET @sql = 'UPDATE ImageStore '
    SET @sql = @sql + 'SET picture = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS picture) '
    SET @sql = @sql + 'WHERE id = ' + STR(@id)

    BEGIN TRY
        EXECUTE sp_executesql @sql 
    END TRY
    BEGIN CATCH

    END CATCH   

    FETCH NEXT FROM image_cursor 
    INTO @id;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;

SELECT * FROM ImageStore

DROP TABLE ImageStore

If you need an example which uses just SQL checkout the following:

It uses a cursor to loop through each of the rows which do not contain image data and for each row found uses the OPENROWSET BULK provider to load the file.

CREATE TABLE ImageStore
( 
id INT, 
picture VARBINARY(MAX) NULL
)
GO 

INSERT INTO ImageStore (id) VALUES(25166)
INSERT INTO ImageStore (id) VALUES(25167)

DECLARE @id INT
DECLARE image_cursor CURSOR FOR 
SELECT id FROM ImageStore WHERE picture IS NULL

OPEN image_cursor;

FETCH NEXT FROM image_cursor 
INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    DECLARE @imagePath NVARCHAR(255)
    SET @imagePath = 'C:\' + RTRIM(LTRIM(STR(@id))) + '.gif'

    SET @sql = 'UPDATE ImageStore '
    SET @sql = @sql + 'SET picture = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS picture) '
    SET @sql = @sql + 'WHERE id = ' + STR(@id)

    BEGIN TRY
        EXECUTE sp_executesql @sql 
    END TRY
    BEGIN CATCH

    END CATCH   

    FETCH NEXT FROM image_cursor 
    INTO @id;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;

SELECT * FROM ImageStore

DROP TABLE ImageStore

回复收藏 0 原文

~没有更多了~