sql - 如何 "deepcopy"行

Question

我的问题类似于this one但更多的参与。假设我有一个表 A，id 为 idA，另一个表 B 有 idB 和外键 idA。我想复制 A 的所有条目，包括 B 中的相应条目。例如，如果我在开头有以下表格:

A
|---|
|idA|
|---|
| 1 |
| 2 |
| 3 |
|---|

B
|---|---|
|idB|idA|
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
|---|---|

那么结果应该是:

A
|---|
|idA|
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
|---|

B
|---|---|
|idB|idA|
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 4 |
| 5 | 4 |
| 6 | 5 |
|---|---|

Answer 1

这很棘手。您需要将 ID 插入到 a 中——但随后能够将它们与现有的 ID 匹配，以将正确的值插入到 b 中。

通用解决方案如下所示:

with i as (
      insert into a
          select . . .  -- the other columns you want
          from a
          order by idA
          returning *
     ),
     a_mapping (
      select a.idA, i.idA as new_idA
      from (select a.*, row_number() over (order by idA) as seqnum
            from a
           ) a join
           (select i.*, row_number() over (order by idA) as seqnum
            from i
           ) i
           on a.seqnum = i.seqnum
     )
insert into b (idA) (
    select am.new_idA
    from b join
         a_mapping am
         on b.idA = am.idA;

注意:如果行中有另一个或多个唯一列，则映射的生成会稍微容易一些。当然，如果您要复制所有列，则没有其他内容是唯一的，因此您确实需要 row_number()。

当然，对于您的非常简单的示例，您不需要映射表。你可以只使用:

with i as (
      insert into a
          select . . .  -- the other columns you want
          from a
          order by idA
          returning *
     )
insert into b (idA) (
    select i.idA
    from i