有没有一种方法可以批量插入数据到两个有外键关系的表中？其中一个表的外键指向另一个表。

Question

我将提供一个伪代码示例，展示我当前的方法。如果有人知道一种不需要逐行操作的方法，我会非常感激。我正在使用MS SQL Server 2008。

define cursor for the data to be inserted (about 3 million records)
loop
(
    insert record into table 1
    use scope_identity() to get key
    insert record into table 2 that references table 1
)

我更愿意同时将数据插入两个表中，因为使用游标和循环的方式速度较慢。

有人可能会抱怨为什么要将数据插入两个具有一对一关系的不同表格，原因是第一个表格无法被修改，而我需要第二个（临时）表格中的信息作为以后进行数据转换操作的参考。

不能在无法修改的表格上添加临时列来存储引用数据，因为这个表格无法被修改。这是一个在线系统，我没有权限改变表格结构。

---

附加信息：

Ex

Source:

1 a big  monkey
2 a tall elephant
3 a big  giraffe
4 a tiny cow
5 a tall cow

Dest:

Table 1       Table 2
monkey        1 big
elephant      2 tall
giraffe       3 big
cow           4 tiny
cow           5 tall

- AppFzx

---

这个答案有帮助吗？(https://dev59.com/MW435IYBdhLWcg3whQg5) - Martin Smith

Answer 1

您可以在Table1上使用merge，并将输出output到Table2中。

merge Table1
using SourceTable
on 0 = 1
when not matched then
  insert (Animal) values (SourceTable.Animal)
output inserted.ID, SourceTable.Size into Table2(ID, Size);

SQL Fiddle

注意： 如果Table2有一个针对Table1定义的外键，那么您不能直接将输出放到Table2中。在这种情况下，您可以使用临时表作为输出的目标，并从临时表插入到Table2中。

- Mikael Eriksson

Answer 2

insert into table1
select substring(src.data, 8 /* assuming fixed length as exampled */, len(src.data))
from source src

insert into table2
select t1.id, substring(src.data, 3 /* skip 'a ' */, 7)
from
    table1 t1
        inner join source src
        on substring(src.data, 8, len(src.data)) = t1.data

对于给定的例子，我无法做得更好...

- Marcus Vinicius Pompeu

Answer 3

有了这么多数据，最好的选择可能是隔离系统上的更新，允许identity insert并在插入之前预填充键。

---

或者，您可以先执行第一次插入，然后修改第二次插入（从临时表）以加入原始数据并查找键。

- cjk

---

很棒的想法，谢谢，但是...我无法修改第一个表以允许标识插入。第二个想法非常完美，但我需要第二个表与第一个表有外键关联的原因是因为我要插入到第一个表中的数据不包含任何可以使每条记录唯一的内容。第二个目标表实质上将包含源表的标识。请参见我添加到问题中的其他信息。 - AppFzx

你不需要修改表来允许标识插入 - 你只需要用 SET IDENTITY_INSERT ON <tablename> 和 SET IDENTITY_INSERT OFF 括起你的插入语句即可。 - Ross Presser

Answer 4

使用IDENTITY_INSERT、VIEW和INSTEAD OF触发器，并在插入时预填充标识值。 请参阅此SQL Fiddle。

我尝试在INSTEAD OF触发器中使用各种方法让T1的标识列用于T2，但最终失败了。

- Ross Presser