Sql Server Insert query Into multiple tables from temporary tables -

i have 2 tables 1 master , 1 details table , details table contain master table id reference

here table

**table_customer**   customerid  customername 1           2           b  **table_customerrelatives**          relativesid  customerid     relativesname   address 1                  1                m        xyz 2                  1                n        mno 3                  2                l        pqr 4                  2                o        ghy 

here customerid , relativesid identity columns automatic generating columnvalue

here have 2 temp tables like

**tembrerytablecustomer**    customerid  customername 1               f 2               g 3               h  **tembrerytabledetails**         customerid  relativesname   address 1              s             fgg 1              t             dfg 2              u             ghj 3              v             jkl 3              w             rty 

how can insert temp table data tables identity. here want insert rows of temp table customer , identity value insert temp table details data using temp table customer identity.

you can use output clause of merge statement build mapping of temporary customer's id new id.

declare @map table (old int, new int);  ;merge  table_customer dest using   tembrerytablecustomer src on      dest.customername = src.customername when    not matched         insert (customername) values (customername) output  inserted.customerid, src.customerid    @map;  insert  table_customerrelatives         (customerid, relativesname, address) select  m.new ,       t.relativesname ,       t.address    tembrerytable2 t join    @map m on      t.customerid = m.old; 

example @ sql fiddle.