sql - 合并两个父>子表集

Question

我需要将两个父>子表集中的数据合并/组合到第三个父>子表中。

表格如下所示:

三组表中的唯一区别是 TableC 有一个 TableType 列，用于帮助辨别 TableA 记录和 TableB 记录之间的差异。

我的第一个想法是使用游标。下面的代码用于创建表结构，插入一些记录，然后将数据合并在一起。它运作得非常好，sooooo....

--Create the tables

CREATE TABLE TableA
(
    ID int not null identity primary key,
    Name VARCHAR(30)
);

CREATE TABLE TableAChild
(
    ID int not null identity primary key,
    Parent int not null,
    Name VARCHAR(30),
    CONSTRAINT FK_A FOREIGN KEY (Parent) REFERENCES TableA(ID)
);

CREATE TABLE TableB
(
    ID int not null identity primary key,
    Name VARCHAR(30)
);

CREATE TABLE TableBChild
(
    ID int not null identity primary key,
    Parent int not null,
    Name VARCHAR(30),
    CONSTRAINT FK_B FOREIGN KEY (Parent) REFERENCES TableB(ID)
);

CREATE TABLE TableC
(
    ID int not null identity primary key,
    TableType VARCHAR(1),
    Name VARCHAR(30)
);

CREATE TABLE TableCChild
(
    ID int not null identity primary key,
    Parent int not null,
    Name VARCHAR(30),
    CONSTRAINT FK_C FOREIGN KEY (Parent) REFERENCES TableC(ID)
);

-- Insert some test records.. 

INSERT INTO TableA (Name) Values ('A1')
INSERT INTO TableAChild (Name, Parent) VALUES ('A1Child', SCOPE_IDENTITY())
INSERT INTO TableB (Name) Values ('B1')
INSERT INTO TableBChild (Name, Parent) VALUES ('B1Child', SCOPE_IDENTITY())

-- Needed throughout.. 
DECLARE @ID INT

-- Merge TableA and TableAChild into TableC and TableCChild
DECLARE TableACursor CURSOR
    -- Get the primary key from TableA
    FOR SELECT ID FROM TableA
OPEN TableACursor
    FETCH NEXT FROM TableACursor INTO @ID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- INSERT INTO SELECT the parent record into TableC, being sure to specify a TableType
        INSERT INTO TableC (Name, TableType) SELECT Name, 'A' FROM TableA WHERE ID = @ID

        -- INSERT INTO SELECT the child record into TableCChild using the parent ID of the last row inserted (SCOPE_IDENTITY())
        -- and the current record from the cursor (@ID).
        INSERT INTO TableCChild(Name, Parent) SELECT Name, SCOPE_IDENTITY() FROM TableAChild WHERE Parent = @ID

        FETCH NEXT FROM TableACursor INTO @ID
    END;

CLOSE TableACursor
DEALLOCATE TableACursor

-- Repeat for TableB
DECLARE TableBCursor CURSOR
    FOR SELECT ID FROM TableB
OPEN TableBCursor
    FETCH NEXT FROM TableBCursor INTO @ID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO TableC (Name, TableType) SELECT Name, 'B' FROM TableB WHERE ID = @ID
        INSERT INTO TableCChild(Name, Parent) SELECT Name, SCOPE_IDENTITY() FROM TableBChild WHERE Parent = @ID
        FETCH NEXT FROM TableBCursor INTO @ID
    END;

CLOSE TableBCursor
DEALLOCATE TableBCursor

现在，我的问题:

• 我总是被告知游标不好。但我找不到另一种方法来做到这一点。我想知道是否有办法用 CTE 来做到这一点？
• 如果光标适合这种情况，我是怎么做的？有更好的方法来做我所做的事情吗？对我来说，它看起来不太干燥，但我不是 SQL 专家。

最后，如果您想重新运行上面的查询，这里有一个小脚本来删除创建的表。

DROP TABLE TableAChild
DROP TABLE TableBChild
DROP TABLE TableCChild

DROP TABLE TableA
DROP TABLE TableB
DROP TABLE TableC

正确的结果应该是这样的:

Answer 1

您可以使用merge正如 Adam Machanic 在 Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE 中所述并在 this问题来获取表变量中新标识值和旧主键值之间的映射，并在插入子表时使用该映射。

declare @T table(ID int, IDC int);

merge dbo.TableC as C
using dbo.TableA as A
on 0 = 1
when not matched by target then
  insert (TableType, Name) values('A', A.Name)
output A.ID, inserted.ID into @T(ID, IDC);

insert into dbo.TableCChild(Parent, Name)
select T.IDC, AC.Name
from dbo.TableAChild as AC
  inner join @T as T
    on AC.Parent = T.ID;

delete from @T;

merge dbo.TableC as C
using dbo.TableB as B
on 0 = 1
when not matched by target then
  insert (TableType, Name) values('B', B.Name)
output B.ID, inserted.ID into @T(ID, IDC);

insert into dbo.TableCChild(Parent, Name)
select T.IDC, BC.Name
from dbo.TableBChild as BC
  inner join @T as T
    on BC.Parent = T.ID;

SQL Fiddle