Imagine uma tabela com três colunas: uma chave primária de ID, um ParentID anulável referenciando a coluna de ID nessa mesma tabela e um TreeID para indicar os registros que compõem uma determinada árvore. Uma estrutura hierárquica básica pode ser assim. Observe que os valores ordinais não necessariamente afetam a posição de um registro na hierarquia.
dbo.TreeNodes Visual representation:
+----+----------+--------+ --------------------------
| ID | ParentID | TreeID | Tree 7: Tree 15:
+----+----------+--------+ 1 10
| 1 | NULL | 7 | |–3 |–9
| 2 | 4 | 7 | |–4
| 3 | 1 | 7 | |–|–2
| 4 | 1 | 7 | |-5
| 5 | 1 | 7 |
| 9 | 10 | 15 |
| 10 | NULL | 15 |
+----+----------+--------+
Dada outra tabela não relacionada com uma definição idêntica ( dbo.TreeNodes2, digamos), descobri que copiar os dados da primeira tabela para a segunda mantendo sua hierarquia auto-referencial não é tão simples. Eu fico preso depois de inserir os novos registros dbo.TreeNodes2 quando chega a hora de preencher os valores ParentID. Eu teria que ter algum tipo de tabela de mapeamento para saber quais registros da primeira tabela mapeiam para os novos registros da segunda tabela. Aqui está o que eu tenho até agora:
DECLARE @TreeID INT = 7;
-- hard code arbitrary TreeID value for the 2nd table for the sake of demo
DECLARE @NewTreeID INT = 42;
CREATE TABLE #InsertedRows
(
ID INT NOT NULL PRIMARY KEY
);
INSERT INTO dbo.TreeNodes2
(
ParentID,
TreeID
)
OUTPUT Inserted.ID INTO #InsertedRows
SELECT
NULL, -- ParentID can't be added yet because the IDs don't yet exist.
-- They need to be updated in later.
@NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;
Eu poderia adicionar uma OldIDcoluna não relacionada a dbo.TreeNodes2 para copiar os IDs originais, que eu poderia usar para criar um mapeamento para ingressar e preencher os novos registros ParentID e, em seguida, anular todos os OldIDvalores quando terminar, mas isso parece um design ruim. Se o SQL Server permitisse que a cláusula OUTPUT de um INSERT incluísse colunas de uma junção, isso seria fácil, mas toda a documentação , bem como meus próprios testes, indicam que apenas Inserted.*colunas, Deleted.*colunas e valores/literais escalares podem ser usado na cláusula OUTPUT. Nada de junções.
Meu sonho, T-SQL inválido que faz o que eu quero, seria algo assim:
CREATE TABLE #InsertedRows
(
ID INT NOT NULL PRIMARY KEY,
OldID INT NOT NULL
);
INSERT INTO dbo.TreeNodes2
(
ParentID,
TreeID
)
OUTPUT Inserted.ID, tn1.ID INTO #InsertedRows (ID, OldID)
SELECT
NULL, -- ParentID can't be added yet because the IDs don't yet exist.
-- They need to be updated in later.
@NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;
A partir daqui, eu teria uma tabela de mapeamento que posso usar para preencher os valores ParentID na nova tabela.
Existe um padrão para entregar esses tipos de inserções hierárquicas entre tabelas sem depender de uma coluna extra cujo único objetivo é permitir a migração, após o que é uma coluna irrelevante?