How to loop using MS SQL?

Question

What I wanted to do is I want to loop through each select result and at the same time use the result to do something while in a loop.

While (select field1,field2,field3 from table 1)
BEGIN
select count(*) as field4 from table1 where field1(current_record)>3
update table1 set field2(current_record)=field4
END

something like that...How do I do this?
Any help would be greatly appreciated.

Answer 1

You have to define what's called a cursor. Then, you can use the FETCH keyword to retrieve the resultset one row at a time and do whatever you need to do with it. The general format is



DEFINE CURSOR cursorName FOR // defines the query that drives the cursor



OPEN cursorName // populates the resultset



FETCH NEXT FROM cursorName INTO // gets the first row of resultset



WHILE @@FETCH_STATUS = 0 // loop until eof

BEGIN



FETCH NEXT FROM cursorName INTO // gets next row of resultset

END



CLOSE cursorName // close the cursor

DEALLOCATE cursorName // release memory allocation

Answer 2

how many tables are you speaking approximately? Will you in general understand what the tables are? Are you doing it to each table in a database? you could loop in sq. Server. yet once you recognize what the tables are and that they do no longer look to be going to alter it is going to be extra user-friendly to in basic terms.. truncate table table1; truncate table table2; truncate table table3; truncate table table4; truncate table table5;

Answer 3

Here is Microsofts documentation with lots of examples



http://msdn.microsoft.com/en-us/library/ms178642.aspx