CODEWITHSUNDEEP
sql-servert-sqlmigrationtemp-tablesinsertion
LittleFunny
LittleFunny

Reputation: 8405

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

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.

Upvotes: 0

Views: 101

Answers (2)

Sharad Venkataraman
Sharad Venkataraman

Reputation: 114

Have you included the extra column in the schema of the temporary table?

create table table1 
(
id int
,name varchar(50)
,age int
)

declare @TemporaryTable table -- or Create table #TemporaryTable
(                             
  id int,                     
  userid int -- defining the extra column                 
);                            

declare @extracolumn as int = 100; 
-- or declare @extracolumn as int = (select value from table where condition)
-- note that subqueries cannot be added directly in the output clause
-- so need to declare and set a variable that holds the value

insert into table1
output  inserted.id,@extracolumn into  @TemporaryTable -- or #TemporaryTable
values(1,'name',10)

select * from @TemporaryTable

Output is 

id  userid
1   100

Upvotes: 1

sunnykachwala
sunnykachwala

Reputation: 56

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.

Upvotes: 2

Related Questions