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