How to migrate data between tables referencing ID?

0

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?

    
asked by anonymous 30.11.2017 / 19:48

1 answer

1

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.

    
03.12.2017 / 18:30