I have given u small eg.
Declare @ID as varchar(500)
Declare @Name as varchar(30)
Declare @MOBILE as varchar(20)
Declare MY_data CURSOR FOR
Select Name ,Mobile from My_table (Nolock)
OPEN MY_data
FETCH NEXT FROM MY_data INTO @Name ,@MOBILE
WHILE @@FETCH_STATUS = 0
BEGIN
Set @ID = (Generate Max ID)
Insert Records @ID,@Name ,@MOBILE
FETCH NEXT FROM MY_data INTO @Name ,@MOBILE
END
CLOSE MY_data
DEALLOCATE MY_data
Here I want to generate a Max ID and Insert the data so I have used the Cursor.
Let me say one thing: DON'T use cursors. They should be your preferred way of killing the performance of an entire system. Most beginners use cursors and don't realize the performance hit they have. They use memory; they lock tables in weird ways, and they are slow. Worst of all, they defeat most of the performance optimization your DBA can do. Did you know that every FETCH being executed has about the same performance of executing a SELECT? This means that if your cursor has 10,000 records, it will execute about 10,000 SELECTs! If you can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster.
Beginner SQL programmers find in cursors a comfortable and familiar way of coding. Well, unfortunately this lead to bad performance. The whole purpose of SQL is specifying what you want, not how it should be done.
I've once rewritten a cursor-based stored procedure and substituted some code for a pair of traditional SQL queries. The table had only 100,000 records and the stored procedure used to take 40 minutes to process. You should see the face of the poor programmer when the new stored procedure took 10 seconds to run!
Sometimes it's even faster to create a small application that gets all the data, proccess it and update the server. T-SQL was not done with loop performance in mind.
If you are reading this article, I need to mention: there is no good use for cursors; I have never seen cursors being well used, except for DBA work. And good DBAs, most of the time, know what they are doing. But, if you are reading this, you are not a DBA, right?