Reputation: 5216
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
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
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