How to track auto-generated id's in select-insert statement

Posted by k rey on Stack Overflow See other posts from Stack Overflow or by k rey
Published on 2010-12-21T20:47:18Z Indexed on 2010/12/21 20:54 UTC
Read the original article Hit count: 240

Filed under:
|

I have two tables detail and head. The detail table will be written first. Later, the head table will be written. The head is a summary of the detail table. I would like to keep a reference from the detail to the head table. I have a solution but it is not elegant and requires duplicating the joins and filters that were used during summation. I am looking for a better solution. The below is an example of what I currently have. In this example, I have simplified the table structure. In the real world, the summation is very complex.

-- Preparation
create table #detail (
      detail_id int identity(1,1)
    , code char(4)
    , amount money 
    , head_id int null
    );

create table #head (
      head_id int identity(1,1)
    , code char(4)
    , subtotal money
    );

insert into #detail ( code, amount ) values ( 'A', 5 );
insert into #detail ( code, amount ) values ( 'A', 5 );
insert into #detail ( code, amount ) values ( 'B', 2 );
insert into #detail ( code, amount ) values ( 'B', 2 );


-- I would like to somehow simplify the following two queries
insert into #head ( code, subtotal )
    select code, sum(amount)
    from #detail
    group by code

update #detail 
  set head_id = h.head_id
from #detail d
  inner join #head h on d.code = h.code

-- This is the desired end result
select * from #detail

Desired end result of detail table:

  detail_id  code  amount  head_id
  1          A     5.00    1
  2          A     5.00    1
  3          B     2.00    2
  4          B     2.00    2

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2005