CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
mshiyam
mshiyam

Reputation: 53

Stored Procedure to select columns and insert into different table

I have 2 tables ex: Green (userID, Name, Address), Red (userID, Name, TemproraryAddress)

I want to 

select from Green table (Name) Where = userID 
AND 
INSERT INTO Red(userID,Name,TemproraryAddress) 
VALUES (@userID,@Name,@TemproraryAddress)

My main question is how to assign selected value into scalar variable?

Note: This example table is to show a sample scenario, not for real world usage...

Upvotes: -1

Views: 17118

Answers (1)

Nick DeVore
Nick DeVore

Reputation: 10166

Do you mean like

insert into Red(userId, Name, TemporaryAddress
select userId, Name, Address
from Green
where userId = @userId

or

declare @userId int, @Name varchar(100), @address varchar(100)

select @userId = userId, @Name = Name, @address = Address from Green where userId = 12345

insert into Red (userId, Name, TemporaryAddress) values (@userId, @Name, @address)

or something else?

...your question is not clear as to what exactly you want to do. What does your existing code look like and what does it not do that you want it to do?

EDIT

One alternate option is to use an insert trigger on the Green table. I'm not sure if you want to essentially match the tables, but if you did, then this would be how to do that:

create trigger green_InsToRed_TRG on Green for insert as

insert into Red (UserId, Name, TemporaryAddress)
select UserId, Name, Address from inserted

Upvotes: 2

Related Questions