How to migrate data between tables referencing ID?

Asked

Viewed 135 times

0

I have a table called Clipping in my database containing various data, including binary data of an image in a column varbinary(max). I need to migrate only the binary data column to another table called Archives and reference the ID of the Filing cabinet in a table column Clipping.

I’ve tried to use OUTPUT:

insert into Arquivos (ARQ_ARQUIVO)
output inserted.ARQ_ID on Clipping.ARQ_ID
select CLP_ARQUIVO from Clipping

How should I proceed?

sql-server

  • What is the purpose of migrating the CLP_ARQUIVO column from one table to another? // In the Clipping table is there a column that uniquely identifies each row? // Do you know FILESTREAM? // I suggest you post more information about the Files and Clipping tables.

  • @Josédiz wants to centralize all the files in a single table, currently the table Clipping stores the information and images of Clipping. We just want to move the images to another table (Files) and leave the information in the Clipping table. As for FILESTREAM I have read about but not implemented.

  • Since it is procedure that will be executed only once, and considering the characteristics of the column to be migrated, a simple solution to be implemented is through the use of cursor. Move one file at a time, including deleting the contents of the CLP_ARQUIVO column from the Clipping table each time. // Remember to backup before starting migration.

  • @Josédiz Thanks for the tip

  • I added the cursor solution to the answer. It is also possible solution set-based, but for this it is necessary that you add the structure of the tables Clipping and Arquivos problem definition. Specifically the primary keys.

1 answer

1


The OUTPUT clause adds lines at the specified destination; does not update lines.

Since it is procedure that will be executed only 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 upgradable as excerpt

for update of CLP_ARQUIVO, ARQ_ID

This works, since in the Clipping table there is a primary key. Otherwise the cursor is considered read-only.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.