I have a table called Clipping in my database containing miscellaneous data, including the binary data of an image in a varbinary(max) column. I need to migrate just the binary data column to another table called Files and reference the File ID in a column of the Clipping table. p>
I tried to use OUTPUT :
insert into Arquivos (ARQ_ARQUIVO)
output inserted.ARQ_ID on Clipping.ARQ_ID
select CLP_ARQUIVO from Clipping
How do I proceed?
The OUTPUT clause adds rows to the entered destination; does not update lines.
As a procedure that will be executed once, and considering the characteristics of the column to be migrated, a simple solution to be implemented is through the use of cursor.
-- código #1
declare Lê_Clipping cursor
local forward_only
for SELECT CLP_ARQUIVO
from Clipping
order by CLP_ID
for update of CLP_ARQUIVO, ARQ_ID;
declare @ARQ_ID int, @CLP_ARQUIVO varbinary(max);
Open Lê_Clipping;
FETCH NEXT
from Lê_Clipping
into @CLP_ARQUIVO;
while @@fetch_status = 0
begin
-- copia a imagem lida para nova linha na tabela Arquivos
INSERT into Arquivos (ARQ_ARQUIVO)
values (@CLP_ARQUIVO);
-- memoriza o ID da nova linha
set @ARQ_ID= scope_identity();
-- insere o ID da imagem na respectiva linha da tabela Clipping
UPDATE Clipping
set CLP_ARQUIVO= null,
ARQ_ID= @ARQ_ID
where current of Lê_Clipping;
-- lê próxima linha de Clipping
FETCH NEXT
from Lê_Clipping
into @CLP_ARQUIVO;
end;
CLOSE Lê_Clipping;
DEALLOCATE Lê_Clipping;
The cursor is declared as upgradeable, as
for update of CLP_ARQUIVO, ARQ_ID
This works as long as there is a primary key in the Clipping table. Otherwise the cursor is considered read-only.