Loading data from one table into two other tables (one detail, one summary) that are related

Question

Suppose that I have a source table like this:

Source
    SourceKey (PK)
    Data1
    Data2
    Amount

And I am loading data from 'Source' into two related tables that look like this:

Summary
    SummaryKey (PK)
    Data1
    Data2
    Amount

Detail
    DetailKey (PK)
    SummaryKey (FK)
    SourceKey (FK)
    Data2
    Amount

EDIT

Ignoring the problem of relating 'Summary' and 'Detail', the ideal insert statements would look like this:

INSERT INTO Summary (Data1, Data2, Amount)
SELECT Data1, Data2, SUM(Amount) FROM Source GROUP BY Data1, Data2

INSERT INTO Detail (SummaryKey, SourceKey, Data2, Amount)
SELECT ???, SourceKey, Data2, Amount FROM Source

I've 'solved' this problem with a pair of temporary holding tables (corresponding to the destination tables) along with a semi-complicated UPDATE statement to relate them by matching on grouping fields (the real example has a much more complicated grouping). I can't help but feel there exists a simpler solution than what I've created.

Any solution would need to work under SQL Server 2000. Thanks for any ideas.

Answer 1

Use triggers to create the row, when an insert is done on your summary table, in the second table. It will let you to fix the foreign keys by one shot.

Answer 2

I was having a think about how to solve this with SCOPE_IDENTITY() or @@IDENTITY, without triggers where possible, but then it struck me - where is the necessity of the Summary table at all?

Could it not be replaced by a view, which in the example would be along the lines of SELECT Data1, Data2, SUM(amount) FROM Detail GROUP BY Data1, Data2 ? (Obviously in your exact example the detail table doesn't have the Data1 field, but I've used poetic license for that!)

Answer 3

What is the purpose of doing this? If you're just doing it for performance reasons it seems like this might be a good candidate for an indexed view.

Answer 4

Since your design leaves much to be desired but is unchangeable. I thinkthe best approach is to use a cursor (boy I hate to suggest this).

Pull the source into a staging table that has an additional column of SummaryKey which will remain blank and an identity key called StagingKey. Using a cursor, insert the records one at a time into summary. After each insert pull the value of of scope_identity() and update the record with the staging key that you just inserted to put this value into the staging table. Once all are done, you should have all you need to insert to the detail table in one insert rather than a cursor.

In newer versions of SQL Server, you could use the output clause and use a set-based insert for the first table too.