如何在更新父记录时插入一组子记录？

Question

我正在使用 SQL Server 2005，希望创建一些地址记录，并使用新的 ID 更新联系人记录：

采取下表

create table contact(id int primary key identity, home_address_id int, work_address_id int)

create table address(id int primary key identity, street varchar(25), number int)

和外键：

ALTER TABLE dbo.contact ADD CONSTRAINT  FK_contact_address1 FOREIGN KEY (home_address_id) REFERENCES dbo.address(id)
ALTER TABLE dbo.contact ADD CONSTRAINT  FK_contact_address2 FOREIGN KEY (work_address_id) REFERENCES dbo.address(id)

一些虚拟数据

insert into contact default values
insert into contact default values
insert into contact default values

如何为所有拥有以下权限的联系人插入默认的空地址记录没有家庭地址，一口气更新home_address_id？

第一部分很简单：

insert into address(street) select null from contact where home_address_id is null

我什至可以获得新创建的地址 ID：

declare @addressTable table(id int)
insert into address(street) 
OUTPUT INSERTED.Id INTO @addressTable
select null from contact where home_address_id is null

这是新的 ID

select * from @addressTable

但如何使用这些新的 ID 更新联系人表？

Answer 1

如果可能的话，我建议通过添加 Contact_Addresses 表来标准化您的数据库：

CREATE TABLE Contact_Addresses
(
    contact_id INT NOT NULL,
    address_id INT NOT NULL,
    address_type VARCHAR(10) NOT NULL,
    CONSTRAINT PK_Contact_Addresses PRIMARY KEY CLUSTERED (contact_id, address_id, address_type),
    CONSTRAINT FK_ContactAddresses_Contacts (contact_id) REFERENCES Contacts (id),
    CONSTRAINT FK_ContactAddresses_Addresses (address_id) REFERENCES Addresses (id),
    CONSTRAINT CK_ContactAddresses_address_type CHECK address_type IN ('HOME', 'WORK')
)

接下来，我建议不要将“虚拟”记录放入数据库中。它最终会引起头痛。数据库应包含系统中数据的准确记录。如果您想在系统中不存在联系人地址时默认显示某些值，请在 UI 中进行处理。

如果你真的必须这样做，那么下面的代码应该可以解决问题：

;WITH C_CTE AS
(
    SELECT
        id,
        home_address_id,
        ROW_NUMBER() OVER(ORDER BY id) AS seq
    FROM
        Contacts
),
(
    SELECT
        id,
        ROW_NUMBER() OVER(ORDER BY id) AS seq
    FROM
        Addresses
)
UPDATE
    C_CTE
SET
    home_address_id = A.id
FROM
    C_CTE C
INNER JOIN A_CTE A ON A.seq = C.seq

Answer 2

我会从您获得新联系人的那一刻起执行此操作，因此：

[receive contact information]
//prior to inserting contact
declare @homeAddress int, @workAddress int

[insert home address here (real or default based on input)]
set @homeAddress = @@Identity

[insert work address here (real or default)]
set @workAddress = @@Identity

[insert contact here referencing @homeAddress & @workAddress]

对于表中已有的内容，您必须将所有空值 ID 关联到联系人 ID。或者，您可以清除空值地址，并以某种方式修改上述语句以进行更新（大脑目前不工作，所以我想到的只是一个游标，而游标是邪恶的）。