SQL: How do I insert data from one table and output to a temporary table with extra value from first table

Question

I can use the OUTPUT keyword of the insert statement to insert new data to a table and output to a temporary table.

The input table which to be inserted into another table have an Id I need to pass to the temporary table but not the table I going to insert into. This temporary table will later have to use to do extra insertion to the other table.

INSERT INTO table1 (Name, Age)
OUTPUT inserted.Id, User.Id (??) INTO TemporaryTable
SELECT Name, Age FROM User

Is there a way to do it? Because the next insertion will need the new table1.Id with the User.Id, so I can migrate some data.

Answer 1

Instead of using the Temporary table you can use Variable so that it will not occupy more memory.

create table table1 
(
id int NOT NULL,
,name varchar(50)
,age int,
 PRIMARY KEY (id)
)

insert into table1 (name,age) values ('name', 10)                          

declare @extracolumn as int =  scope_identity() 
select @extracolumn 

use this @extracolumn in next insert operation.