Geek Answers Handbook

Trying to avoid RBAR! How to correlate an insert in a linked table as a set-based operation?

Assume that the diagram consists of the following tables:

Baz

BazID (PK, Identity) Description

FooTemplate (Baz can have zero for many FooTemplates)

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

BarTemplate (FooTemplate can have zero for many BarTemplates)

 BarTemplateID (PK, Identity) FooTemplateID (FK) Description

Foo (Baz can have zero before many Foos)

 FooID (PK, Identity) BazID (FK) Description

Bar (A Foo can have zero for many bars)

 BarID (PK, Identity) FooID (FK) Description

Each day, the stored procedure will be executed to generate Foo and Bar objects for the associated Baz object, which passed the next generation date.

The first part of this procedure looks something 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 kind of statement can I execute to create the correct Bar entities, and are they linked correctly with the newly created Foo objects?

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

EDIT2: Thanks everyone for the help. After carefully studying the information, I chose another solution. I changed the primary key in the Foo table so that it would no longer be an automatically generated identity column, so that intermediate insertion into the temporary table could be done to capture the corresponding FooTemplateID along with the FooID

+6
source share
2 answers

If I understand your scheme 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 offer another solution, since it will depend on the uniqueness of the combination (BazID, Description) in the FooTemplate table. It can also be rewritten by a variable table for fooTemplate with a date <@ Give it if that 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
+2
source

This makes the assumption large that there is only one FooTemplate for BASID and only one BarTemplate for FooTemplate. If this is not the case, structures make it difficult to determine which baz comes with which foo templated and which bartemplate comes with which footemplate - you will have to drop into Description columns to ensure uniqueness, and without PK or UQ you cannot be sure 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

According to @RomanPekar, if you can add FooTemplateId to the temp table, this later update will be easier, but you are right, the output suggestion only works on inserted and deleted (and it becomes very fussy when you start working with new identification columns)

[Added]

Here's how Id summarizes the main problem:

  • You add a lot of rows to the Foo table
  • Each row has a surrogate key just generated (FooId)
  • Now for each of these new Foo objects, zero or more Bar strings should now be created
  • So, each of your new lines should be mapped to the original FooTemplate line in order to get the "original" FooTemplate necessary BarTemplate lines
  • But there is nothing in the Foo table to enable this mapping (for example, to associate Foo with the original FooTemplate. Cant use BazId because there can be several templates; cant-well, shouldnt-use description because it does not guarantee uniqueness
  • And this link cannot be provided by the INSERT ... OUTPUT ... construct, since it can only retrieve columns inserted in the Foo table

One solution that Ive used in the past for this problem is to temporarily populate a column in a new table (Foo) with the binding data. Here Id stores FooTemplateId in Foo.Description, create the necessary INSERT ... SELECT based on this, and then update the Foo associated with FooTemplate with this id and replace the description with the correct value. It works, but it is inconvenient.

An alternative solution based on @MikaelErikssons deleted deleted messages and links (especially How did I learn to stop worrying and love MERGE ) is to use the merge command, which (who knew?) Can refer to columns outside inserted and deleted in its output. Use this to populate temp table with a new FooID and source FooTemplateId and work from there. (Without sample data for testing and debugging, I'm not sure of my MERGE skills to try and write this process, but I'm sure it can be done.)

+1
source

More articles:


All Articles