CODEWITHSUNDEEP
sqlsql-serverstored-procedures
RollerCosta
RollerCosta

Reputation: 5216

foreach on selected rows SQL stored procedure

I have a table that is basically set up so there is an id(UNIQUE),mapID,employe_ID, and a value... it looks sort of like this

Table

ID    mapID       employee_id     value
1       1         1               Brian
2       1         1               617-555-5555
3       1         2               Boston Office
4       1         2               Mary
5       3         1               617-666-6666
6       3         2               New york office

I want to loop thru this table so "for each" same employee_id, I can do an insert statement into another table where it is setup a little cleaner.

Like this will give you rough idea of what i am trying to convey.

Select * from table where employe_id=1
foreach(item in aboveSelect)
{
   Insert into table2
}


Obviously this is not the correct syntex.

Upvotes: 1

Views: 3478

Answers (2)

marc_s
marc_s

Reputation: 755541

Don't "foreach loop" over SQL data - use the data as sets. So in your case - do it this way:

INSERT INTO dbo.Table2(Col1, Col2, ...., ColN)
  SELECT Col1, Col2, ...., ColN
  FROM dbo.table 
  WHERE employee_id=1

Nice, fast, set-oriented....

Upvotes: 3

Callie J
Callie J

Reputation: 31336

No need for a loop/cursor - you can do this as a single atomic statement: 

INSERT INTO table2 (col1, col2, col3)
    SELECT id, mapid, employee_id FROM table1

...this is also faster and cleaner that a cursor-based approach. Looping in SQL should be avoided unless there is no other option as SQL Server prefers a set-based approach to most problems.

Upvotes: 2

Related Questions