SQL-Server: How do I insert multiple rows with data from a temp table + store the ID of the inserted rows in the temp table -

i trying insert 1 row audit table every row in temporary table , store id of each audit table row in temporary table.

the columns inserted audit table come rows in temporary table , need refer audit.id via temporary table (so need match correctly)

i not want use loops.

without example tables, data, , desired results, here attempt @ solution using output , table variable.

create table t (id int not null identity(1,1), val varchar(32), audit_id int null); insert t (val) values ('three'),('two'),('one'); create table audit (id int not null identity(1,1), val varchar(32));  /* table variable output */ declare @output table (id int, val varchar(32));  /* insert output */ insert audit (val) output inserted.id, inserted.val @output select val t;  /* updated t output */ update t  set audit_id = o.id t   inner join @output o     on t.val = o.val;  select * audit; select * t; 

rextester demo: http://rextester.com/jmot34416

for audit table, returns:

+----+-------+ | id |  val  | +----+-------+ |  1 | 3 | |  2 | 2   | |  3 | 1   | +----+-------+ 

and temporary table t

+----+-------+----------+ | id |  val  | audit_id | +----+-------+----------+ |  1 | 3 |        1 | |  2 | 2   |        2 | |  3 | 1   |        3 | +----+-------+----------+