How can I insert a set of child records while updating the parent?

Question

I'm using SQL 2005 and wish to create a number address records, updating the contact records with the new Id's:

Take the following tables

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)

And foreign keys:

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)

some dummy data

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

How can I insert a default empty address record for all contacts who have no home address, and update the home_address_id in one go?

The first part is simple:

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

I can even get the newly create address id's:

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

Here's the new id's

select * from @addressTable

But how to update the contact table with these new Id's?

Answer 1

I would do it from the moment you get a new contact, thusly:

[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]

For the stuff already in your table, you're going to have to associate all of your null value ids to a contact id. Or, you could clear out your null value addresses, and modify the above statement to an update somehow (brain's not working at the moment, so all I'm coming up with is a cursor, and cursors are evil).

Answer 2

If possible, I would suggest normalizing your database by adding a Contact_Addresses table:

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

Next, I would suggest not putting "dummy" records in your database. It's going to end up causing headaches down the road. The database should contain an accurate record of the data in your system. If you want to display some value by default when no address exists in the system for a contact then handle that in your UI.

If you really must though, then the following code should do the trick:

;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