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
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
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:
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.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With