sql - INSERT into table 和 UPDATE 另一个表中的外键(Sql Server 2008)

Question

我有两张 table ，TableA和 TableB像这样:

TableA
-------------------------------------------
|  id  |  some_data  | new_FK_column_on_B |
| ---- | ----------- | ------------------ |
|   1  |     ...     |        null        |
|  ... |     ...     |        null        |
|  999 |     ...     |        null        |
-------------------------------------------

TableB
----------------------------
|  id  |  some_other_data  |
| ---- | ----------------- |
|      |                   |
----------------------------

目前， TableB为空，FK 列在 TableA是 null对于所有行。我需要编写一次性初始化脚本来填充 TableB并为 TableA 中的某些行(标准，不是全部)初始化 FK 列通过行中的标识符，插入 TableB .

我知道有两种方法可以做到这一点:

1) 使用 while和 scope_identity() , 将新行插入 TableB和更新 TableA在每次迭代中，同时存在于 TableA 中的行，应该更新

while (exists (select 1 from TableA where [condition]))
begin
    insert into TableB (some_other_data) values ('some_other_data')

    update TableA set new_FK_column_on_B
    where id = (select top 1 id from TableA where [condition])
end

2) 在 TableB 中创建临时列, 存储 id行的 TableA ，为其插入，然后更新 TableA使用 join

alter table TableB add temp int
go

insert into TableB (some_other_data, temp) select 'some_other_data', id from TableA where [condition]

update TableA
set new_FK_column_on_B = b.id
from TableB as b
join TableA as a on a.id = b.temp

alter table TableB drop column temp

我也试图以某种方式使用 output来自 insert像这样，但它的语法不正确:

update TableA
set new_FK_column_on_B =
(
    select insertedId from 
    (
        insert into TableB (some_other_data)
        output inserter.id as insertedId
        values ('some_other_data')
    )
)
where [condition]

不使用 while 有没有更简单的方法可以做到这一点或修改任何表？

Answer 1

我在寻找类似案例的解决方案时发现了这个问题。唯一的区别是我想为 TableA 中的每一行填充一行(没有 where 子句)。

我找到了您建议的第三个选项的解决方案(使用 insert 的输出)，但是，由于您对来自 SELECT 的数据使用 INSERT，因此不能使用 OUTPUT 子句。 This为我指明了正确的方向:

DECLARE @MyTableVar table(tableBId int, tableAId int)

MERGE INTO TableB
using TableA AS AP
on 1=0
WHEN NOT MATCHED THEN
   Insert(some_other_data) 
           Values('some_other_data')
           Output inserted.ID, AP.ID INTO @MyTableVar;

update TableA set new_FK_column_on_B = (select tableBId from @MyTableVar where tableAId = TableA.ID)

请注意，执行此操作几秒钟将在 TableB 中创建新条目。如果您只想在 TableB 中创建新行，而 TableA 中没有设置外键，则可以使用此脚本:

DECLARE @MyTableVar TABLE(tableBId int, tableAId int)

MERGE INTO TableB AS B
USING TableA AS AP
ON A.new_FK_column_on_B = B.id
WHEN NOT MATCHED THEN
   INSERT(some_data) 
           VALUES(AP.some_data)
           OUTPUT inserted.ID, AP.ID INTO @MyTableVar;

UPDATE TableA SET new_FK_column_on_B = (
   SELECT tableBId
   FROM @MyTableVar
   WHERE tableAId = TableA.ID )
WHERE TableA.new_FK_column_on_B IS NULL;