INSERT in two tables based on SELECT

Question

Imagine the following scenario:

At a certain point it is necessary to duplicate people and their respective items.

If I have the following data:

Table Person

+----+------------+
| Id | Nome       |
+----+------------+
| 1  | Joãozinho  |
| 2  | Mariazinha |
+----+------------+

Table Person

+----+------------+--------+
| Id | PessoaId   | ItemId |
+----+------------+--------+
| 1  | 1          | 1      |
| 2  | 1          | 2      |
| 3  | 2          | 1      |
+----+------------+--------+

Item Table

+----+------------+
| Id | Descricao  |
+----+------------+
| 1  | Item1      |
| 2  | Item2      |
+----+------------+

One can conclude from this that Joãozinho owns the Item1 and Item2, and Mariazinha has only the Item1.

By duplicating the data, we would have:

Table Person

+----+------------+
| Id | Nome       |
+----+------------+
| 1  | Joãozinho  |
| 2  | Mariazinha |
| 3  | Joãozinho  |
| 4  | Mariazinha |
+----+------------+

Table Person

+----+------------+--------+
| Id | PessoaId   | ItemId |
+----+------------+--------+
| 1  | 1          | 1      |
| 2  | 1          | 2      |
| 3  | 2          | 1      |
| 4  | 3          | 1      |
| 5  | 3          | 2      |
| 6  | 4          | 1      |
+----+------------+--------+

Item Table Remains as before.

To duplicate only one table is easy, just run the following script:

INSERT INTO Pessoa
(Nome)
SELECT Nome
FROM Pessoa

But in this case, which involves more than one table? What is the best way to do this?

I am using SQL Server.

Answer 1

DECLARE @campo VARCHAR(30)
    , @campo2 VARCHAR(30)
    , @campo3 VARCHAR(60)

-- Cursor para percorrer os nomes dos objetos 
DECLARE itens CURSOR FOR
    SELECT campo, campo2, campo3 
    FROM
        tabela_origem


-- Abrindo Cursor para leitura
OPEN itens

-- Lendo a próxima linha
FETCH NEXT FROM itens INTO @campo, @campo2, @campo3

-- Percorrendo linhas do cursor (enquanto houverem)
WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO TABELA2 VALUES( @campo, @campo2, @campo3 )

    -- Lendo a próxima linha
  FETCH NEXT FROM itens INTO @campo, @campo2, @campo3
END

-- Fechando Cursor para leitura
CLOSE itens

-- Desalocando o cursor
DEALLOCATE itens