Sql Server Insert Query into Multiple Tables from Temporary Tables


I have two tables one is master and another one is details table , Details table contain master table id as reference

Here is my table

CustomerId  CustomerName
1           A
2           B

RelativesId  CustomerId     RelativesName   Address
1                  1                M        xyz
2                  1                N        mno
3                  2                L        pqr
4                  2                O        ghy

Here CustomerId and RelativesId are identity columns so automatic generating columnvalue

Here I have two temp tables like

CustomerId  CustomerName
1               F
2               G
3               H

CustomerId  RelativesName   Address
1              S             fgg
1              T             dfg
2              U             ghj
3              V             jkl
3              W             rty

How can Insert Temp table data to my tables with identity. here i want to insert rows of temp table customer and get identity value then insert temp table details data using temp table customer identity.

You can use the output clause of the merge statement to build a mapping of the temporary customer's id to the new id.

declare @Map table (Old int, New int);

;merge  Table_Customer dest
using   TembreryTableCustomer src
on      dest.CustomerName = src.CustomerName
when    not matched then
        insert (CustomerName) values (CustomerName)
output  inserted.CustomerID, src.CustomerID
into    @map;

insert  Table_CustomerRelatives
        (CustomerId, RelativesName, Address)
select  m.New
,       t.RelativesName
,       t.Address
from    TembreryTable2 t
join    @Map m
on      t.CustomerID = m.Old;

Example at SQL Fiddle.