合并两个父>子表集

I need to get the data in two parent > child table sets merged/combined into a third parent > child table.

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

The tables look like this:

表格如下所示：

The only difference in the three sets of tables is that TableC has a TableType column to help discern the difference between a TableA record and a TableB record.

这三组表的唯一区别是TableC有一个TableType列来帮助识别TableA记录和TableB记录之间的差异。

My first thought was to use a cursor.. Here's code to create the table structure, insert some records, and then merge the data together. It works very well, sooooo....

我的第一个想法是使用游标..这是创建表结构，插入一些记录，然后将数据合并在一起的代码。它工作得非常好，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

Now, my question(s):

现在，我的问题：

• I've always been told that cursors are bad. But I couldn't find another way of doing it. I'm wondering if there's some way to do that with a CTE?
• 我总是被告知游标很糟糕。但我找不到另一种方法。我想知道是否有一些方法可以用CTE做到这一点？
• If the cursor is appropriate in this situation, how did I do? Is there a better way of doing what I did? It doesn't look very DRY to me, but I'm no SQL expert.
• 如果光标在这种情况下是合适的，我该怎么办？做我做的更好的方法是什么？它对我来说看起来不太干，但我不是SQL专家。

Lastly, if you want to re-run the query above, here's a small script to delete the tables that were created.

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

DROP TABLE TableAChild
DROP TABLE TableBChild
DROP TABLE TableCChild

DROP TABLE TableA
DROP TABLE TableB
DROP TABLE TableC

The correct result should look like:

正确的结果应如下所示：

6 个解决方案

#1

Here is one way to do this without a cursor or other RBAR type stuff.

这是一种在没有游标或其他RBAR类型的情况下执行此操作的方法。

ALTER TABLE TableC ADD LegacyID INT
GO

INSERT INTO TableC (TableType, Name, LegacyID)
SELECT 'A', Name, ID
FROM TableA

INSERT TableCChild
SELECT C.ID, AC.Name
FROM TableAChild AC
JOIN TableA A ON A.Id = AC.ID
JOIN TableC C ON C.LegacyID = A.ID AND C.TableType = 'A'

INSERT INTO TableC (TableType, Name, LegacyID)
SELECT 'B', Name, ID
FROM TableB

INSERT TableCChild
SELECT C.ID, AC.Name
FROM TableBChild AC
JOIN TableB A ON A.Id = AC.ID
JOIN TableC C ON C.LegacyID = A.ID AND C.TableType = 'B'

ALTER TABLE TableC DROP COLUMN LegacyID
GO

#2

You can use merge as described by Adam Machanic in Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE and in this question to get a mapping between the new identity value and the old primary key value in a table variable and the use that when you insert to your child tables.

您可以使用Adam Machanic在OUTPUT博士中所描述的合并，或者：我如何学会停止担心和喜欢MERGE，并在此问题中获取新标识值与表变量中的旧主键值之间的映射，以及插入子表时使用它。

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

SQL小提琴

#3

You can use a map table to link the old and new ids together based on some key.

您可以使用映射表根据某些键将旧的和新的ID链接在一起。

In my example, I am using the order of insertion into TableC.

在我的例子中，我使用插入TableC的顺序。

1. Create a map table with an identity column.
2. 使用标识列创建映射表。
3. Add data in TableC table based on order of ID of TableA and get the inserted ids in the map
4. 根据TableA的ID顺序在TableC表中添加数据，并在地图中获取插入的ID
5. Use the same order of TableA.id to get a ROWNUMBER() and match it with the identity column of the map table and update the old_id in map to match TableA.id with TableC.id .
6. 使用相同的TableA.id顺序获取ROWNUMBER（）并将其与map表的标识列匹配，并更新map中的old_id以使TableA.id与TableC.id匹配。
7. Use the map to insert into the TableCChild table
8. 使用映射插入TableCChild表
9. Truncate the map and rinse and repeat for other tables.
10. 截断地图并冲洗并重复其他表格。

Sample Query

示例查询

CREATE TABLE  #map(id int identity,new_id int,old_id int);
INSERT INTO TableC
(
    TableType,
    Name
)output inserted.id into #map(new_id)
SELECT 'A',Name
FROM TableA
ORDER BY ID


update m
set m.old_id = ta.id
FROM #map m
inner join 
(
select row_number()OVER(order by id asc) rn,id
from tableA
)ta on ta.rn = m.id

INSERT INTO TableCChild (Name, Parent) 
SELECT Name,M.new_ID
FROM #Map M
INNER JOIN TableAChild TA ON M.old_id = TA.Parent

TRUNCATE TABLE #map

INSERT INTO TableC
(
    TableType,
    Name
)output inserted.id into #map(new_id)
SELECT 'B',Name
FROM TableB
ORDER BY ID

update m
set m.old_id = tb.id
FROM #map m
inner join 
(
select row_number()OVER(order by id asc) rn,id
from tableB
)tb on tb.rn = m.id

INSERT INTO TableCChild (Name, Parent) 
SELECT Name,M.new_ID
FROM #Map M
INNER JOIN TableBChild TB ON M.old_id = TB.Parent

DROP TABLE #Map

#4

I just wrote the following SQL to do it if the Name is unique in TableA and unique in TableB

如果Name在TableA中是唯一的并且在TableB中是唯一的，我只是编写了以下SQL来执行此操作

INSERT INTO TableCChild
  (
    Parent,
    NAME
  )
SELECT tc.ID,
       ta.Name
FROM   TableAChild  AS ta
       JOIN TableA a
            ON  a.ID = ta.Parent
       JOIN TableC  AS tc
            ON  tc.Name = a.Name
                AND tc.TableType = 'A' 
UNION
SELECT tc.ID,
       tb.Name
FROM   TableBChild  AS tb
       JOIN TableB b
            ON  b.ID = tb.Parent
       JOIN TableC  AS tc
            ON  tc.Name = b.Name
                AND tc.TableType = 'B' 

If Name is not unique and only the ID is the Unique Identifier then I would add the LegacyId as suggested and the code would then be as follows

如果Name不是唯一的，只有ID是唯一标识符，那么我会按照建议添加LegacyId，然后代码如下

/* Change Table C to Have LegacyId as well and this is used to find the New Key for Inserts
CREATE TABLE TableC
(
    ID            INT NOT NULL IDENTITY PRIMARY KEY,
    TableType     VARCHAR(1),
    LegacyId     INT,
    NAME          VARCHAR(30)
);
*/

INSERT INTO TableC (Name, TableType, LegacyId) 
SELECT DISTINCT NAME,
       'A', 
       Id
FROM   TableA
UNION
SELECT DISTINCT NAME,
       'B',
       Id
FROM   TableB

    INSERT INTO TableCChild
      (
        Parent,
        NAME
      )
    SELECT tc.ID,
           ta.Name
    FROM   TableAChild  AS ta
           JOIN TableA a
                ON  a.ID = ta.Parent
           JOIN TableC  AS tc
                ON  tc.LegacyId = a.Id
                    AND tc.TableType = 'A' 
    UNION
    SELECT tc.ID,
           tb.Name
    FROM   TableBChild  AS tb
           JOIN TableB b
                ON  b.ID = tb.Parent
           JOIN TableC  AS tc
                ON  tc.LegacyId = b.Id
                    AND tc.TableType = 'B' 

#5

We can reach this by turning the Identity column off till we finish the insertion like the following example.

我们可以通过关闭Identity列来实现此目的，直到我们完成插入，如下例所示。

--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())

SET IDENTITY_INSERT TableC ON
INSERT INTO TableC(ID, TableType, Name)
SELECT ID, 'A', Name FROM TableA

INSERT INTO TableCChild(Parent, Name)
SELECT Parent, Name FROM TableAChild

DECLARE @MAXID INT
SELECT @MAXID = MAX(ID) FROM TableC
PRINT @MAXID

SET IDENTITY_INSERT TableC ON
INSERT INTO TableC(ID, TableType, Name)
SELECT ID + @MAXID, 'B', Name FROM TableB
SET IDENTITY_INSERT TableC OFF

INSERT INTO TableCChild(Parent, Name)
SELECT Parent + @MAXID, Name FROM TableBChild

SET IDENTITY_INSERT TableC OFF

SELECT * FROM TableC
SELECT * FROM TableCChild

DROP TABLE TableAChild
DROP TABLE TableBChild
DROP TABLE TableCChild

DROP TABLE TableA
DROP TABLE TableB
DROP TABLE TableC

#6

If you need to insert records in third table TableC and TableCChild for later use then it's fine to insert data in these tables but if you only need this table data to use it in your stored procedure for the time being then you can also just work with first two tables to get the desired result.

如果你需要在第三个表TableC和TableCChild中插入记录供以后使用，那么可以在这些表中插入数据，但是如果你只需要这个表数据暂时在你的存储过程中使用它，那么你也可以使用它前两个表来获得所需的结果。

select * from (
select a.ID,'A' as TableType,a.Name from TableA a inner join TableAChild b on a.ID=b.ID
union
select a.ID,'B' as TableType,a.Name  from TableB a inner join TableBChild b on a.ID=b.ID) TableC

Similarly get TableCChild

同样得到TableCChild

select * from 
(
select b.ID,b.Parent,b.Name  from TableA a inner join TableAChild b on a.ID=b.ID
union
select b.ID,b.Parent,b.Name   from TableB a inner join TableBChild b on a.ID=b.ID) TableCChild

And if you have to insert in TableC and TableCChild then you have to recreate TableC with primary key on ID and TableType, and turn off the identity for ID column.

如果必须在TableC和TableCChild中插入，则必须使用ID和TableType上的主键重新创建TableC，并关闭ID列的标识。