CODEWITHSUNDEEP
sql-serverstored-procedures
MattP
MattP

Reputation: 2863

SQL Server stored procedure - SELECT statement then use results in INSERT statement

I am writing a stored procedure that will get results from one table then copy them to another. It isn't an exact match, I'm changing 1 column and ignoring another. There are 5 columns and on average 3-5 results if that is relevant.

I basically need to:

SELECT * FROM sometable WHERE somecolumn = 1

Then for every result

INSERT INTO anothertable (a,b,c) VALUES (@a, @b, @c)

What is the best way to do this within a stored procedure?

Upvotes: 2

Views: 5785

Answers (3)

Bridge
Bridge

Reputation: 30651

You can insert directly from a select query:

INSERT INTO anothertable (a,b,c)
SELECT @a, @b, @c FROM sometable WHERE somecolumn = 1

Upvotes: 2

Călin Darie
Călin Darie

Reputation: 6237

I would try something like 

insert into anothertable (a,b,c)
select a, b, c from sometable where somecolumn = 1

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147234

You can do this in one statement:

INSERT AnotherTable (a, b, c)
SELECT a, b, c
FROM SomeTable
WHERE SomeColumn = 1

Wherever possible, avoid doing things in loops/cursors/RBAR (Row By Agonizing Row) and instead try to think in SET-based approaches like above.

Upvotes: 6

Related Questions