sql server - SQL : insert data from one table into two tables related to each other -

i have table tsource select result of cartesian product, there no unique id in set. in short lets table looks following:

tsource ------- f1 | f2 -------  h |   |  b  j |  c  k |  d  k |  d 

i need "split" data of tsource tbl1 , tbl2 related each other:

tbl1             tbl2 -------          ----------------- id | f1          id | tbl1_id | f2 -------          ----------------- 11 |  h          51 |      11 |  12 |           52 |      12 |  b 13 |  j          53 |      13 |  c 14 |  k          54 |      14 |  d 15 |  k          55 |      15 |  d 

id columns in both destination tables int identity

any appreciated, thanx in advance

do 2 insertions operations altogether in merge + output statement.

merge @table2 t2 using (     select *     @table ) src on (1 = 2) when not matched     insert (f1)     values (src.f1) output inserted.id, src.f2 @table3 (f1id, f2) ; 

complete example:

declare @table table (     f1 char(1)     , f2 char(1) )  insert @table values ('h', 'a') , ('i', 'b') , ('j', 'c') , ('k', 'd')   declare @table2 table (     id int not null identity     , f1 char(1) )  declare @table3 table (     id int not null identity     , f1id int not null     , f2 char(1) )  merge @table2 t2 using (     select *     @table ) src on (1 = 2) when not matched     insert (f1)     values (src.f1) output inserted.id, src.f2 @table3 (f1id, f2) ;  select * @table2  select * @table3