Imagine the following scenario:
At some point you need to duplicate people and their items.
If I have the following data:
Person Table
+----+------------+ | Id | Nome | +----+------------+ | 1 | Joãozinho | | 2 | Mariazinha | +----+------------+
PersonItem Table
+----+------------+--------+ | Id | PessoaId | ItemId | +----+------------+--------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 1 | +----+------------+--------+
Table Item
+----+------------+ | Id | Descricao | +----+------------+ | 1 | Item1 | | 2 | Item2 | +----+------------+
It can be concluded that Joãozinho has Item1 and Item2 , and Mariazinha has only Item1 .
Duplicating the data, we would have:
Person Table
+----+------------+ | Id | Nome | +----+------------+ | 1 | Joãozinho | | 2 | Mariazinha | | 3 | Joãozinho | | 4 | Mariazinha | +----+------------+
PersonItem Table
+----+------------+--------+ | Id | PessoaId | ItemId | +----+------------+--------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 1 | | 4 | 3 | 1 | | 5 | 3 | 2 | | 6 | 4 | 1 | +----+------------+--------+
Table Item It remains as before.
To duplicate just 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'm using SQL Server.