Trying to avoid RBAR! How to correlate an insert on a related table as a set based operation?

Question

Assume a schema that consists of the following tables:

Baz

BazID (PK, Identity)
Description

FooTemplate (A Baz may have zero to many FooTemplates)

FooTemplateID (PK, Identity)
BazID (FK)
Description
NextGenerationDate

BarTemplate (A FooTemplate may have zero to many BarTemplates)

BarTemplateID (PK, Identity)
FooTemplateID (FK)
Description

Foo (A Baz may have zero to many Foos)

FooID (PK, Identity) 
BazID (FK)
Description

Bar (A Foo may have zero to many Bars)

BarID (PK, Identity)
FooID (FK)
Description

Each day a stored procedure will execute to generate Foo and Bar entities for an associated Baz entity which have passed their next generation date.

The first part of this procedure looks a little like this:

DECLARE @GeneratedFooIDList TABLE (INT FooID);
INSERT Foo (BazID, Description)
OUTPUT inserted.FooID INTO @GeneratedFooIDList
SELECT
  BazID
  Description
FROM
  FooTemplate
WHERE
  NextGenerationDate < GETDATE()

My question is what statement can I now execute to generate the proper Bar entities and have them properly associated with the newly created Foo entities?

EDIT: The procedure will be executing on a server running SQL Server 2005.

EDIT2: Thanks to everybody for the help. After considering the information carefully, I have opted for another solution. I have changed the primary key in the Foo table to no longer be an automatically generated identity column, this way an intermediary insert into a temporary table could be executed to capture the relevant FooTemplateID along with the FooID

Answer 1

If I understand your schema correctly

declare @GeneratedFooIDList table (FooID int, FooTemplateID int)
declare @Date datetime = getdate()

/*
insert Foo (BazID, Description)
    output inserted.FooID, FT.FooTemplateID into @GeneratedFooIDList
select
    FT.BazID,
    FT.Description
from FooTemplate as FT
where
    FT.NextGenerationDate < @Date
*/

merge into Foo using
(
    select *
    from FooTemplate as FT
    where
        NextGenerationDate < @Date
) as FT on 1 = 0
when not matched then
insert (BazID, Description)
values (BazID, Description)
output inserted.FooID, FT.FooTemplateID into @GeneratedFooIDList;

insert Bar (FooID, Description)
select
    G.FooID
    BT.Description
from BarTemplate as BT
    inner join @GeneratedFooIDList as G on G.FooTemplateID = BT.FooTemplateID

well, if you have SQL Server 2005, then this will not work. I can suggest another solution, by it will depends on uniqueness of combination (BazID, Description) in FooTemplate table. It also may be rewritten with variable table for fooTemplate with date < @Date if it helps.

http://sqlfiddle.com/#!3/ee576/29

declare @GeneratedFooIDList table (FooID int)
declare @Date datetime = getdate()

insert Foo (BazID, Description)
    output inserted.FooID into @GeneratedFooIDList
select
    FT.BazID,
    FT.Description
from FooTemplate as FT
where
    FT.NextGenerationDate < @Date

insert Bar (FooID, Description)
select
    G.FooID,
    BT.Description
from @GeneratedFooIDList as G
    inner join Foo as F on F.FooID = G.FooID
    inner join FooTemplate as FT on FT.BazID = F.BazID and FT.Description = F.Description
    inner join BarTemplate as BT on BT.FooTemplateID = FT.FooTemplateID

Answer 2

This makes the big assumption that there is only one FooTemplate per BazID, and only one BarTemplate per FooTemplate. If this is not the case, the structures make it hard to identify which baz goes with which foo templated, and which bartemplate goes with which footemplate -- you'd have to toss in the Description columns to ensure uniqueness, and without PKs or UQs you can't be certain of uniqueness.

INSERT Bar (FooID, Description)
 select new.FooId, bt.Description
  from @GeneratedFooIDList new
   inner join Foo f
    on f.FooId = new.FooId
   inner join FooTemplate  ft  --  one to one assumption here
    on ft.BazID = f.BazID
   inner join BarTemplate bt  --  another one to one assumption here
    on bt.FooTemplateId = ft.FooTemplateId

As per @RomanPekar, if you could add the FooTemplateId in the temp table this later update would be simpler, but you are correct, the output clause only works on inserted and deleted (and it gets very fussy when you start dealing with new identity columns)

---

[Added]

Here’s how I’d summarize the core problem:

• You add many rows to the Foo table
• Each row has a freshly generated surrogate key (FooId)
• Zero or more Bar rows must now be generated for each of these new Foo entities
• So, each of your new rows must be mapped back to its original FooTemplate row, so as to get the "source" FooTemplate's necessary BarTemplate rows
• But, there is nothing in the Foo table to enable this mapping (e.g. to link Foo with its source FooTemplate. Can’t use BazId, because there could be multiple templates; can’t—well, shouldn’t—use description, because does not guarantee uniqueness
• Nor can this link be provided by the INSERT… OUTPUT… construct, as it can only extract columns inserted into the Foo table

One solution I’ve used in the past for this kind of problem is to temporarily populate a column in the new table (Foo) with the linking data. Here, I’d store FooTemplateId in Foo.Description, build the necessary INSERT…SELECTs based on that, and then updated Foo linked to FooTemplate on that id and replace description with the proper value. It works, but it is awkward.

An alternate solution, based on @MikaelEriksson’s unfortuitously deleted post and referenced links (especially How I Learned to Stop Worrying and Love the MERGE ), is to use the merge command, which (who knew?) can reference columns outside of inserted and deleted in its output clause. Use that to populate the temp table with new FooID and source FooTemplateId, and work from there. (Without sample data to test and debug, I’m not confident enough in my MERGE skills to try and write this process, but I am confident that it could be done.)