Select to retrieve multiple rows then loop results for Insert statement

Question

I need to create a stored procedure to do the following;

    SELECT * FROM TABLE1

Then for each row returned;

    INSERT INTO TABLE2 (column1, column2, column3,...) 
    VALUES (value1, value2, value3,...)

How would I go about doing this?

UPDATE **

So based on responses so far I've got the following sql;

INSERT INTO recEntrantStatus (entrantId, roundId, judgeId)
SELECT entrantId, judgeId
FROM recEntrantStatus
WHERE roundId = 0
AND voted = true
AND enterNextround = true

But I need to add a fixed value eg an input @round into the newly created records in the column roundId.

How do I go about doing that?

Thanks.

------------------------------------- UPDATE 2 -------------------------------------------

So thanks to some significant help from you guys, I've now got the following SQL which works great at this stage:

  INSERT INTO recEntrantStatus (entrantId, roundId, judgeId, notified, voted, enterNextRound)
  SELECT entrantId, (@round + 1), judgeId, 0, 0, 0
  FROM recEntrantStatus
  WHERE roundId = @round
  AND voted = 1
  AND enterNextround = 1

This code checks for all records where 'enterNextRound' is true, and then creates a new record for each of them.

However, I now need to expand this so that:

A) Check a second table(tblJudges) for all judges and get an array of all the judges Id's (Id)

B) Do the same as in the above example except now creating each of the above records for each of the judges / judges Id's obtained from step A.

Any help / suggestions would as always be greatly appreciated.

Thanks!

CLOSED - Question posted as a new question due to additional requirements. Thanks!

INSERT INTO recEntrantStatus (entrantId, roundId, judgeId) SELECT entrantId, @round, judgeId FROM ... — KM.
– KM., Commented Oct 9, 2012 at 17:19
@KM Thats what I was assuming would be the answer. Thanks for the confirmation!!! — Phill Healey
– Phill Healey, Commented Oct 10, 2012 at 12:20
@KM Please see my revised question. Ive now expanded the scenario to a slightly more complex scenario. Massive thanks for your help so far. — Phill Healey
– Phill Healey, Commented Oct 10, 2012 at 14:29

Brad Christie · Accepted Answer · 2012-10-10 12:41:53Z

3

CREATE PROCEDURE myProcedure
AS
    INSERT INTO table2 (col1, col2, col3)
    SELECT      col1, col2, col3
    FROM        table1
GO

Or something similar should work. SQL Server allows you to perform an INSERT directly from a SELECT statement.

Note that this really doesn't need to be a stored procedure unless there's more going in to it (business logic or conditions you want static across all implementations).

Update

CREATE PROCEDURE myprocedure @roundId DECIMAL(5,2)
AS
  INSERT INTO   recEntrantStatus
               (entrantId, roundId, judgeId)
  SELECT        entrantId, roundId, judgeId
  FROM          recEntrantStatus
  WHERE         roundId = @roundId
    AND         voted = 1
    AND         enterNextRound = 1
GO

edited Oct 10, 2012 at 12:41

answered Oct 9, 2012 at 16:02

Brad Christie

102k16 gold badges160 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Phill Healey Over a year ago

Excellent thanks. Presumably this works with WHERE conditions applied also? I just put everything into SP's for clarity and continuity across the project. Thanks again. Ill give this a go now.

Brad Christie Over a year ago

@PhillHealey: Yes, you can apply a WHERE condition to the SELECT.

Phill Healey Over a year ago

Please see my updated question so I can squeeze a bit more info out of you. Thanks. ;-)

Brad Christie Over a year ago

@PhillHealey: Would you like the argument as a stored procedure parameter, or just a static value inserted into the table?

Phill Healey Over a year ago

It would be an input parameter.

|

Tobsey · Accepted Answer · 2012-10-09 16:01:57Z

1

INSERT has two allowed syntaxes. The one you gave above is one but you can also do:

INSERT INTO
    Table1(col1, col2, col3)
SELECT
    Col1,
    Col2,
    Col3
FROM
    Table2

answered Oct 9, 2012 at 16:01

Tobsey

3,40016 silver badges24 bronze badges

1 Comment

Phill Healey Over a year ago

Please see my revised question. Ive now expanded the scenario to a slightly more complex scenario. Massive thanks for your help so far.

Collectives™ on Stack Overflow

Select to retrieve multiple rows then loop results for Insert statement

2 Answers 2

Update

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related