What to use in SQL instead of a "Foreach" loop

Question

I'm trying to write a stored procedure in SQL that will:

1. Make a select query from table1 that will return multiple values
2. Insert new values in table2 (1 new record in table2 for each record returned by the select on table1).

I would use a foreach in C# but I know that SQL doesn't work that way. What's the correct way of doing this?

Answer 1

INSERT INTO tabl2 (name, id)
   SELECT name, id FROM table1

Loops can indeed be very useful in SQL, so you may want to know how to do that as well. Here's one example:

DECLARE @temp TABLE (ix int identity(1,1), id int, name varchar(100))

INSERT INTO @temp SELECT id, name FROM table1

DECLARE @i int, @max int

SELECT
   @i = 0
   @max = MAX(ix)
FROM
   @temp

WHILE @i < @max
BEGIN
   SET @i = @i + 1

   -- LOGIC HERE...

END