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
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.