时间戳

首页 > 解决方案 > 使用光标将批量图像导入 SQL Server

sql - 使用光标将批量图像导入 SQL Server

问题描述

我正在尝试起草一个 SQL 脚本，该脚本将循环将图像文件插入到基于 ID 匹配的 SQL Server 表中。我根据数据库中的 customerID 命名了图像文件。我想找出一种方法来遍历成员 ID 并将图像插入我的表中。以下是我当前的代码。

Declare @CUSTCD int, @EVENTCD nvarchar(50), @SIGNATURES varbinary(max)

SET @SIGNATURES = Select BulkColumn from Openrowset (Bulk 'C:\sigs\'+ @CUSTCD int+ '.png', Single_Blob) as Image

-- declare a cursor DECLARE insert_cursor CURSOR FOR select @CUSTCD,'75thCelebration', BulkColumn from Openrowset (Bulk 'C:\sigs\'+(Select @CUSTCD from CUS)+'119.png', Single_Blob) as Image --SELECT CUSTCD , EVENTCD, SIGNATURES from CUS_SIGS2 --WHERE CUSTCD = 78

-- open cursor and fetch first row into variables OPEN insert_cursor FETCH NEXT FROM insert_cursor into @CUSTCD, @EVENTCD, @SIGNATURES

-- check for a new row WHILE @@FETCH_STATUS=0 BEGIN -- do complex operation here Insert into CUS_SIGS2
SELECT @CUSTCD, @EVENTCD, @SIGNATURES -- get next available row into variables FETCH NEXT FROM insert_cursor into @CUSTCD, @EVENTCD, @SIGNATURES END close insert_cursor Deallocate insert_cursor GO

标签： sqlsql-serverloopstsqlfetch

解决方案

对不起，但你的代码是一团糟。我认为这大致是你想要做的：

declare @CUSTCD int, @EVENTCD nvarchar(50), @SIGNATURES varbinary(max)

declare insert_cursor cursor for select CUSTCD from CUS

open insert_cursor  
fetch next from insert_cursor into @CUSTCD 

while @@fetch_status = 0  
begin  

    set @SIGNATURES = Select BulkColumn from Openrowset (Bulk 'C:\sigs\'+ @CUSTCD int+ '.png', Single_Blob) as Image

    -- "do complex operation here Insert into CUS_SIGS2"

    fetch next from insert_cursor into @CUSTCD 
end 

close insert_cursor  
deallocate insert_cursor

虽然我不知道@EVENTCD应该从哪里来。

推荐阅读