Insert Into... Merge... Select (SQL Server) -

i use expertise. have following code:

insert table3 (column2, column3, column4, column5) select null, 110, table1.id, table2.column2 table1      join table1table2link on table1.id=table1table2link.column1      join table2 on table1table2link.column2=table2.id 

now need take inserted.id (table3's identity generated on insert) , table2.id , insert them either temporary table or table variable. use output clause, output cannot data across different tables. believe can done merge not sure how go it. need like:

insert table3 (column2, column3, column4, column5) output inserted.id, table2.id @mytablevar select null, 110, table1.id, table2.column2 table1      join table1table2link on table1.id=table1table2link.column1      join table2 on table1table2link.column2=table2.id 

i apologize if duplicate question not find anything.

the trick populate table merge statement instead of insert...select. allowes use values both inserted , source data in output clause:

merge table3 using (     select null col2,             110 col3,             table1.id col4,             table2.column2 col5,            table2.id col6     table1     join table1table2link on table1.id=table1table2link.column1     join table2 on table1table2link.column2=table2.id ) s on 1 = 0 -- not matched when not matched insert (column2, column3, column4, column5) values (s.col2, s.col3, s.col4, s.col5) output inserted.id, s.col6 @mytablevar (insertedid, table2id);