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

Question

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

Make a select query from table1 that will return multiple values 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?

Thanks!

Answer 1

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

EDIT

I should add, that 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

Answer 2

SQL is a set-based language. What you do is express the result you want as a query - that returns a 'set' which is then inserted into the new table.

You CAN use a CURSOR which is row-by-row based but that really is a last resort. Very very rarely have I found that to be the only way.

maybe if you posted soem more detail about your problem we can help!

Answer 3

You can use a select as the values for an insert

INSERT INTO Table1
(
    FieldA,
    FieldB
)
SELECT
    FieldA,
    FieldB
FROM Table2

Answer 4

What you want is an insert / select statement, not a for each. If you find yourself needing to iterate over rows to do something, you should be thinking "how can I accomplish the same in a set-based solution." Failure to think set-based when working in SQL can lead you down the path to performance problems very quickly.

INSERT Table2
       (Col1,
        Col2)
SELECT T1Col1,
       T1Col2
FROM   Table1
WHERE  T1Col2 = 'whatever'

Answer 5

No "foreach" needed in this case. For the rare cases that you do, look into cursors and the while-loop. The syntax is so awkward that you'll automatically avoid them if you can :)

For your insert, something like this should do:

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

(copied for completeness' sake)